Posts

Showing posts from January, 2018

Close DropShip Query

-
Close DropShip Query SELECT   poh.segment1 po_number,          po_headers_sv3.get_po_status (poh.po_header_id) po_status,          hou_po.name po_org_name,          jrd.resource_name buyer_name,          hzp_vendor.party_name vendor_name,          poh.attribute1 po_carrier,          hou.name org_name,          (SELECT   at.name             FROM   po_headers_all pha, ap_terms at            WHERE   at.term_id(+) = pha.terms_id                    AND pha.po_header_id = poh.po_header_id)             po_payment_terms,          ooh.order_number,          ool.line_number so_line_n...
Post a Comment
Read more

Open Dropship Query

-
Open Dropship Query SELECT   poh.segment1 po_number,          po_headers_sv3.get_po_status (poh.po_header_id) po_status,          hr.name po_operating_unit,          ppl.full_name buyer,          pv.vendor_name supplier,          poh.attribute1 po_carrier,          hr1.name operating_unit,          (SELECT   at.name             FROM   po_headers_all pha, ap_terms at            WHERE   at.term_id(+) = pha.terms_id                    AND pha.po_header_id = poh.po_header_id)             po_payment_terms,          oeh.order_number,          oel.line_number so_line_no,   ...
Post a Comment
Read more

To Display Amount in Words by Using below Script

-
To Display Amount in Words by Using below Script CREATE OR REPLACE FUNCTION AMOUNT_TO_WORDS (P_AMT IN NUMBER)    RETURN VARCHAR2 IS    M_MAIN_AMT_TEXT     VARCHAR2 (2000);    M_TOP_AMT_TEXT      VARCHAR2 (2000);    M_BOTTOM_AMT_TEXT   VARCHAR2 (2000);    M_DECIMAL_TEXT      VARCHAR2 (2000);    M_TOP               NUMBER (20, 5);    M_MAIN_AMT          NUMBER (20, 5);    M_TOP_AMT           NUMBER (20, 5);    M_BOTTOM_AMT        NUMBER (20, 5);    M_DECIMAL           NUMBER (20, 5);    M_AMT               NUMBER (20, 5);    M_TEXT              VARCHAR2 (2000); BEGIN    M_...
Post a Comment
Read more

Script to Search the Related Vales in View

-
Script to Search the Related Vales in View DECLARE    v_text   VARCHAR2 (32000); BEGIN    FOR i IN (SELECT VIEW_NAME, text, TEXT_LENGTH                FROM ALL_VIEWS               WHERE VIEW_NAME LIKE '%XX%' AND TEXT_LENGTH < 31000)    LOOP       v_text := i.text;       IF    (INSTR (UPPER (v_text), 'SERVIC') > 0)          OR (INSTR (UPPER (v_text), '90') > 0)             OR (INSTR (UPPER (v_text), 'DEPL') > 0)          OR (INSTR (UPPER (v_text), 'CONS') > 0)          OR (INSTR (UPPER (v_text), '1265') > 0)          OR (INSTR (UPPER (v_text), '2365') > 0)          OR (INSTR (UPPER (v_text), '2569') > 0)         ...
Post a Comment
Read more

Sales Order Ship to Bill to Address

-
Sales Order Ship to Bill to Address  ------------------------------------------------------------- SELECT ooh.order_number,       hp_bill.party_name,      hps_bill.PARTY_SITE_NUMBER      , hl_ship.address1 ||Decode(hl_ship.address2,NULL,'',chr(10))       ||hl_ship.address2||Decode(hl_ship.address3,NULL,'',chr(10))       ||hl_ship.address3||Decode(hl_ship.address4,NULL,'',chr(10))       ||hl_ship.address4||Decode(hl_ship.city,NULL,'',chr(10))       ||hl_ship.city    ||Decode(hl_ship.state,NULL,'',',')       ||hl_ship.state   ||Decode(hl_ship.postal_code,'',',')       ||hl_ship.postal_code ship_to_address      , hl_bill.address1 ||Decode(hl_bill.address2,NULL,'',chr(10))       ||hl_bill.address2||Decode(hl_bill.address3,NULL,'',chr(10))       ||hl_bill.address3||Decode(hl_...
Post a Comment
Read more

Supplier Payment History Sample Query

-
Supplier Payment History Sample Query SELECT aps.vendor_name,          aia.invoice_num,          TO_CHAR (aia.invoice_date, 'DD-MON-YYYY') invoice_date,          aia.invoice_type_lookup_code,          aia.description,          aia.invoice_currency_code,          aia.payment_currency_code,          aia.invoice_amount,          aia.amount_paid,          aca.check_number,          TO_CHAR (aca.check_date, 'DD-MON-YYYY') check_date     FROM ap_invoices_all aia,          ap_suppliers aps,          ap_invoice_payments_all aip,          ap_checks_all aca    WHERE     aia.vendor_id = aps.vendor_id       ...
Post a Comment
Read more