Customer PO Conversion

Customer PO Conversion

Description

            This blog is used to create a Customer PO conversion. This blog will invoke the API OE_BLANKET_PUB.process_blanket and it processes the header and line level information with validations.

-- This query is used to retrieves the customer account related information’s

SELECT   hca.account_number, hcasa.cust_account_id, hcasa.attribute4 custpo

FROM hz_cust_acct_sites_all hcasa, hz_cust_accounts hca

WHERE 1 = 1

AND hca.cust_account_id = hcasa.cust_account_id

AND hcasa.attribute_category = 'ARCUS'

AND hca.account_number = NVL (p_cust_acct_i, hca.account_number)

AND hcasa.attribute4 IS NOT NULL

AND EXISTS

    (

    SELECT DISTINCT purchase_order

    FROM ra_customer_trx_all rct,

    ra_cust_trx_types_all rctta

    WHERE 1 = 1

    AND rct.cust_trx_type_id = rctta.cust_trx_type_id

    AND UPPER (LTRIM (RTRIM (rctta.description))) LIKE '%OSDP%INV%'

    AND rct.complete_flag = 'Y'

    AND rct.purchase_order IS NOT NULL

    AND TRUNC (rct.trx_date) BETWEEN TO_DATE('04-01-2015','MM-DD-YYYY')

    AND TO_DATE('04-30-2015','MM-DD-YYYY')

    AND rct.purchase_order = hcasa.attribute4

    )

AND hcasa.status = 'A'

AND hca.status = 'A'

AND NOT EXISTS

    (

    SELECT cust_po_number

    FROM ont.oe_blanket_headers_all obha

    WHERE cust_po_number = hcasa.attribute4

    AND obha.sold_to_org_id = hcasa.cust_account_id

    )

GROUP BY

    hca.account_number, hcasa.cust_account_id, hcasa.attribute4;

-- This query is used to retrieves the customer and site related

   Information’s

SELECT hps.party_site_number, hp.party_name, hca.account_number,

       hca.status cust_account_status, hcasa.status cust_site_status,

       hcasa.attribute4 custpo, hcsua.site_use_code, hcsua.site_use_id

  FROM hz_parties hp,

       hz_cust_accounts hca,

       hz_locations hl,

       hz_party_sites hps,

       hz_cust_acct_sites_all hcasa,

       hz_cust_site_uses_all hcsua

 WHERE 1 = 1

   AND hp.party_id = hca.party_id

   AND hp.party_id = hps.party_id

   AND hl.location_id = hps.location_id

   AND hca.cust_account_id = hcasa.cust_account_id

   AND hps.party_site_id = hcasa.party_site_id

   AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

   AND hcsua.site_use_code = 'SHIP_TO'

   AND hcasa.attribute_category = 'ARCUS'

   AND hcasa.attribute4 IS NOT NULL

   AND hcasa.cust_account_id = c_cust_acct_id_i     -- Parameter 1

   AND hcasa.attribute4 = c_custpo_i                -- Parameter 2

   AND hcasa.status = 'A'

   AND hcsua.status = 'A'

   AND hca.status = 'A';

-- This API is used to process the customer po with validations

oe_msg_pub.initialize;

oe_blanket_pub.process_blanket

(

    p_org_id                  => n_organization_id,

    p_operating_unit          => NULL,

    p_api_version_number      => 1.0,

    x_return_status           => x_return_status,

    x_msg_count               => x_msg_count,

    x_msg_data                => x_msg_data,

    p_header_rec              => l_hdr_rec,

 -- sold_to_org_id, order_type_id, context, start_date_active, cust_po_number

    p_header_val_rec          => l_hdr_val_rec,

 -- oe_blanket_pub.g_miss_header_val_rec

    p_line_tbl                => l_line_tbl,

 -- oe_blanket_pub.g_miss_blanket_line_rec

    p_line_val_tbl            => l_line_val_tbl,

 -- oe_blanket_pub.g_miss_blanket_line_val_rec

    p_control_rec             => l_control_rec,

    x_header_rec              => x_header_rec,

    x_line_tbl                => x_line_tbl

);

-- By

-- Eswaramoorthi M