query to find supplier address and email id in oracle apps

 SELECT DISTINCT
    aps.vendor_name,
    aps.segment1    supplier_number,
    assa.org_id,
    assa.vendor_site_code,
    (
        SELECT
            hcp.email_address
        FROM
            hz_party_sites     hps,
            hz_contact_points  hcp
        WHERE
                1 = 1
            AND hcp.owner_table_name = 'HZ_PARTY_SITES'
            AND hps.party_id = aps.party_id
            AND hcp.owner_table_id = hps.party_site_id
            AND hcp.contact_point_type = 'EMAIL'
            AND ROWNUM <= 1
    )               email_id,
    assa.address_line1,
    assa.address_line2,
    assa.address_line3,
    assa.city,
    assa.state,
    assa.zip,
    assa.country
FROM
    apps.ap_suppliers             aps,
    apps.ap_supplier_sites_all    assa
WHERE
        1 = 1
    AND aps.vendor_id = assa.vendor_id
    AND aps.segment1 = '28015';

What is the difference between purchased and purchasable flag for an item?

 If both purchased and purchasable attribute are enabled, we can create new purchasing documents and approve and receive the items.

 

If purchased is enabled and purchasable is disabled, we cannot create purchasing documents and approve but we can receive the item against approved purchasing documents.

 

We can enable purchasable only when purchased is enabled.

PO Assign Approval Groups Query

select distinct hr.name "Operating Unit",
 --gs.CURRENCY_CODE,pap.EMPLOYEE_NUMBER,pap.full_name,
HR_GENERAL.DECODE_POSITION_LATEST_NAME(paa.POSITION_ID) Position,--pog.CONTROL_GROUP_NAME position,
hbg.BUSINESS_GROUP_NAME "Organization",
pj.name "Job",
pcf.CONTROL_FUNCTION_NAME "Document Type",
POG.CONTROL_GROUP_NAME "Approval Group" ,
ppc.START_DATE "Effective From",
ppc.END_DATE "Effective To"
--pcr.*
--pcr.RULE_TYPE_CODE,pcr.OBJECT_CODE,pcr.AMOUNT_LIMIT,
--segment1_high||segment2_high||segment3_high||segment4_high||segment5_high||segment6_high||segment7_high||segment8_high "High",
--segment1_low||segment2_low||segment3_low||segment4_low||segment5_low||segment6_low||segment7_low||segment8_low "low"
from po_control_groups_all pog,
po_control_functions pcf,
PO_POSITION_CONTROLS_all ppc,
PER_JOBS pj,
--PO_CONTROL_RULES pcr,
hr_operating_units hr,
PER_ALL_ASSIGNMENTS_F paa,
per_all_people_f pap,
hrfv_business_groups hbg
--gl_ledgers gs
where pog.CONTROL_GROUP_ID= ppc.CONTROL_GROUP_ID
and ppc.CONTROL_FUNCTION_ID=pcf.CONTROL_FUNCTION_ID
and pog.org_id=ppc.org_id
and pj.job_id=ppc.job_id
--and pcr.CONTROL_GROUP_ID=pog.CONTROL_GROUP_ID
--and nvl(pcr.INACTIVE_DATE,sysdate)>=sysdate
and hr.organization_id=pog.org_id
and paa.job_id=pj.job_id
and paa.person_id=pap.person_id
and  nvl(pap.EFFECTIVE_END_DATE,sysdate)>=sysdate
--and gs.ledger_id=hr.SET_OF_BOOKS_ID
and ppc.POSITION_ID=paa.position_id
and ppc.position_id is not null
and  nvl(paa.EFFECTIVE_END_DATE,sysdate)>=sysdate
and paa.ASSIGNMENT_STATUS_TYPE_ID=1
and hr.BUSINESS_GROUP_ID = hbg.BUSINESS_GROUP_ID
--and hr.name='OU_ENVS_BHA_US'
--and HR_GENERAL.DECODE_POSITION_LATEST_NAME(PAa.POSITION_ID) like 'SLA-MEM PERF  PL-OPR.%'
-- and rownum=1
order by hr.name--,pap.employee_number
,HR_GENERAL.DECODE_POSITION_LATEST_NAME(paa.POSITION_ID)
--,pcf.CONTROL_FUNCTION_NAME
,ppc.START_DATE

PO Approver Group Query

SELECT   HOU.NAME,
         PCGA.CONTROL_GROUP_NAME,
         PCGA.DESCRIPTION,
         PCR.OBJECT_CODE,
         PCR.RULE_TYPE_CODE,
         PCR.AMOUNT_LIMIT,
            PCR.SEGMENT1_LOW
         || '.'
         || PCR.SEGMENT2_LOW
         || '.'
         || PCR.SEGMENT3_LOW
         || '.'
         || PCR.SEGMENT4_LOW
         || '.'
         || PCR.SEGMENT5_LOW
         || '.'
         || PCR.SEGMENT6_LOW
         || '.'
         || PCR.SEGMENT7_LOW
         || '.'
         || PCR.SEGMENT8_LOW
            "LOW_VALUE",
            PCR.SEGMENT1_HIGH
         || '.'
         || PCR.SEGMENT2_HIGH
         || '.'
         || PCR.SEGMENT3_HIGH
         || '.'
         || PCR.SEGMENT4_HIGH
         || '.'
         || PCR.SEGMENT5_HIGH
         || '.'
         || PCR.SEGMENT6_HIGH
         || '.'
         || PCR.SEGMENT7_HIGH
         || '.'
         || PCR.SEGMENT8_HIGH
            "HIGH_VALUE"
  FROM   PO_CONTROL_GROUPS_ALL PCGA,
         PO_CONTROL_RULES PCR,
         HR_OPERATING_UNITS HOU
 WHERE       PCGA.CONTROL_GROUP_ID = PCR.CONTROL_GROUP_ID
             and PCGA.ORG_ID =HOU.ORGANIZATION_ID
         --AND PCGA.CONTROL_GROUP_NAME = 'ENVS Buyer AG'
         --AND PCGA.ORG_ID = 103
         order by HOU.NAME,
         PCGA.CONTROL_GROUP_NAME

Lot Controlled Material (Link between po_headers_all, pa_projects_all, mtl_system_items_b, mtl_material_transactions,mtl_transaction_lot_val_v)

select pha.segment1 ENVS_PO_NUM,
pla.line_num,
msi.segment1 ENVS_ITEM,
msi.description ENVS_ITEM_DESC,
msi.primary_uom_code ENVS_UOM,
msi.item_type BULK_ISSUE,
rsh.receipt_num,
rsh.creation_date ENVS_RECEIPT_DATE,
to_char(sysdate,'MON-RR') ENVS_ROTATION_DATE,
--rsh.shipment_header_id,
--rsl.shipment_line_id,
--rt.transaction_id,
--mmt.transaction_id,
mtlv.lot_number ENVS_LOT_NUM,
mtlv.transaction_quantity ENVS_Quantity,
mtlv.primary_quantity,
ppa.segment1 ENVS_PROJECT,
pt.task_name ENVS_TASK,
pv.vendor_name ENVS_SUPPLIER_NAME,
mir.revision ENVS_REVISION
from po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_distributions_all pda,
pa_projects_all ppa,
pa_tasks pt,
ap_suppliers pv,
mtl_system_items_b msi,
mtl_item_revisions_vl mir,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
rcv_transactions rt,
mtl_material_transactions mmt,
mtl_transaction_lot_val_v mtlv,
fnd_user fu
where pha.po_header_id =pla.po_header_id
and pha.org_id =pla.org_id
and pha.po_header_id =plla.po_header_id
and pla.po_line_id =plla.po_line_id
and pha.org_id =plla.org_id
and pla.po_line_id = pda.po_line_id
and pha.po_header_id = pda.po_header_id
and pda.project_id = ppa.project_id(+)
and pda.task_id = pt.task_id (+)
and pha.vendor_id = pv.vendor_id
and pla.item_id = msi.inventory_item_id
and pha.po_header_id = rsl.po_header_id
and msi.organization_id = rsh.ship_to_org_id
and msi.organization_id = ship_to_organization_id
and pla.po_line_id = rsl.po_line_id
and rsl.shipment_header_id = rsh.shipment_header_id
and rt.po_header_id= rsl.po_header_id
and rt.po_line_id = rsl.po_line_id
and rt.shipment_header_id= rsh.shipment_header_id
and rt.shipment_line_id =rsl.shipment_line_id
and rt.organization_id = rsh.ship_to_org_id
and rt.transaction_id = mmt.rcv_transaction_id
and mmt.transaction_id  = mtlv.transaction_id
and msi.inventory_item_id = mtlv.inventory_item_id
and msi.organization_id = mtlv.organization_id
and msi.inventory_item_id = mir.inventory_item_id
and msi.organization_id = mir.organization_id
&lp_recpt_num
--and ppa.segment1 is not null
--and pha.segment1 ='700034534'
--and rsh.receipt_num in ('1010835471','1010833388')
and msi.SERIAL_NUMBER_CONTROL_CODE =1
--and mmt.transaction_id=17867826
and rsh.created_by = fu.user_id
and mir.IMPLEMENTATION_DATE  = (select max(mirv.IMPLEMENTATION_DATE) from  mtl_item_revisions_vl mirv where mirv.INVENTORY_ITEM_ID = mir.INVENTORY_ITEM_ID
                                and  mir.organization_id =mirv.organization_id )
order by rsh.receipt_num ,mtlv.lot_number;

Buyer query (link between po_agents and per_all_people_f)

select per.full_name "Buyer Name" ,pa.START_DATE_ACTIVE "Begin Date",pa.END_DATE_ACTIVE "End Date",per.employee_number
from po_agents pa,
     per_all_people_f per
where 1=1
and pa.AGENT_ID = per.person_id
AND SYSDATE between per.effective_start_date AND per.effective_end_date
order by per.employee_number

supplier unpaid query( Iink between po_headers_all and ap_invoices_all)

  SELECT   DISTINCT ou.name "OPERATING UNIT",
                    pv.segment1 "Supplier GSL",
                    pv.vendor_name "Supplier Name"  ,
                    apt1.name "Supplier Header Terms",
                    apt.NAME "Supplier Site Terms",
                    ph.segment1 "PO#",
                    ph.CLOSED_CODE "PO Status",
                    (SELECT   name
                       FROM   ap_terms
                      WHERE   term_id = ph.terms_id)
                       "PO Terms",
                    aia.invoice_num,
                    (SELECT   name
                       FROM   ap_terms
                      WHERE   term_id = aia.terms_id)
                       "AP Terms",
                  decode(CANCELLED_DATE,null,'', 'Cancelled')   "AP Status"
    --      ,apt.description
    --      ,pvsa.PAY_DATE_BASIS_LOOKUP_CODE "Pay Date Basis"
    FROM   apps.AP_SUPPLIERS pv,
           apps.AP_SUPPLIER_SITES_ALL pvsa,
           apps.ap_invoices_all aia,
          -- apps.ap_invoice_payments_all aip,
           ap_invoice_distributions_all aida,
           (SELECT   *
              FROM   apps.ap_terms_tl
             WHERE   lANGUAGE = 'US') apt,
           (SELECT   *
              FROM   apps.ap_terms_tl
             WHERE   lANGUAGE = 'US') apt1,
           po_headers_all ph,
           po_lines_all pla,
           po_distributions_all pda,
           hr_operating_units ou,
           ap_tolerance_templates att
   WHERE       pv.vendor_id = pvsa.vendor_id
           AND pv.vendor_id = aia.vendor_id
           AND pvsa.VENDOR_SITE_ID = aia.VENDOR_SITE_ID
           AND ou.organization_id = pvsa.org_id
          -- AND aip.invoice_id = aia.invoice_id
           AND pvsa.TERMS_ID = apt.TERM_ID(+)
           AND pv.TERMS_ID = apt1.TERM_ID(+)
           AND pvsa.TOLERANCE_ID = att.TOLERANCE_ID(+)
           AND ph.vendor_id = pv.vendor_id
           AND ph.org_id = pvsa.org_id
           AND ph.po_header_id = pla.po_header_id
           AND ph.org_id = pla.org_id
           AND ph.po_header_id = pda.po_header_id
           AND pla.po_line_id = pda.po_line_id
           AND ph.org_id = pda.org_id
           AND aida.invoice_id = aia.invoice_id
           AND pda.org_id = aida.org_id
           AND aia.org_id = aida.org_id
           AND pda.po_distribution_id = aida.po_distribution_id
           AND aia.PAYMENT_STATUS_FLAG = 'N'
           AND NVL (pv.end_DATE_ACTIVE, SYSDATE) >= SYSDATE
         -- and ph.segment1 ='700021845'
          --and CANCELLED_DATE is null
         --  AND pv.segment1 = 'E52029'
ORDER BY   pv.segment1 ASC

Supplier Extract Query

select distinct ou.name "OPERATING UNIT"
       ,pv.segment1            "Supplier Number"
       ,pv.vendor_name        "Supplier Name"
       ,PV.END_DATE_ACTIVE  "Inactive Date"
--       ,pv.NUM_1099 "Taxpayer ID"
--       ,pvsa.VAT_REGISTRATION_NUM "Tax Registration Number"
--       ,PV.ORGANIZATION_TYPE_LOOKUP_CODE "Organization Type"
--       ,pvsa.country "Site Country"
       ,pvsa.VENDOR_SITE_CODE "Site Name"
--       ,pvsa.ADDRESS_LINE1         "Site Address Line 1"
--       ,pvsa.ADDRESS_LINE2         "Site Address Line 2"
       --,pvsa.ADDRESS_LINE3         "Address Line3"
--       ,pvsa.CITY              "Site City"
--       ,pvsa.STATE             "Site State"
--       ,pvsa.PROVINCE          "Province"
--       ,pvsa.ZIP               "Postal Code"
--       ,pvsa.INACTIVE_DATE     "Site Status"
--       ,pvsa.AREA_CODE          "Phone Area Code"
--       ,pvsa.PHONE              "Phone Number"
--       ,pvsa.FAX              "Fax Number"             
--       ,pvsa.FAX_AREA_CODE      "Fax Area Code"
--       ,pvsa.EMAIL_ADDRESS        "Email Address"
--       ,PVSA.PAY_SITE_FLAG      "Address Purpose Payment"
--       ,PVSa.PURCHASING_SITE_FLAG "Address Purpose Purchasing"
       ,pvsa.PAYMENT_METHOD_LOOKUP_CODE "Site Payment Method"
       ,pv.PAYMENT_METHOD_LOOKUP_CODE "Header Payment Method"
--       ,pvsa.INVOICE_CURRENCY_CODE "Invoice Currency"
--       ,pvsa.PAYMENT_CURRENCY_CODE "Payment Currency"
--       ,pvsa.PAY_GROUP_LOOKUP_CODE "Pay Group"
--       ,att.TOLERANCE_NAME "Invoice Tolerance"  -- pvsa.TOLERANCE_ID
--       ,pv.MATCH_OPTION "Invoice Match Option"
       ,apt.NAME    "Site Payment Terms"
       ,apt1.name "Header Payment Terms"
     -- ,apt.NAME                        "Payment Terms"
--      ,apt.description
--      ,pvsa.PAY_DATE_BASIS_LOOKUP_CODE "Pay Date Basis"
from apps.AP_SUPPLIERS pv
    ,apps.AP_SUPPLIER_SITES_ALL pvsa
   -- ,apps.ap_invoices_all aia
    --,apps.ap_invoice_payments_all aip
    ,(select * from apps.ap_terms_tl where lANGUAGE = 'US') apt
    ,(select * from apps.ap_terms_tl where lANGUAGE = 'US') apt1
    ,hr_operating_units ou
    ,ap_tolerance_templates att
where  pv.vendor_id=pvsa.vendor_id
--AND pv.vendor_id=aia.vendor_id
--AND pvsa.VENDOR_SITE_ID = aia.VENDOR_SITE_ID
AND ou.organization_id=pvsa.org_id
--AND  trunc(aia.CREATION_DATE) BETWEEN nvl(trunc(to_date(:cp_trx_date_low,'yyyy/MM/DD HH24:MI:SS')),trunc(aia.CREATION_DATE))
--                                   AND     nvl(trunc(to_date(:cp_trx_date_high,'yyyy/MM/DD HH24:MI:SS')),trunc(aia.CREATION_DATE))
--AND aip.invoice_id = aia.invoice_id
and  pvsa.TERMS_ID = apt.TERM_ID(+)
AND  pv.TERMS_ID = apt1.TERM_ID(+)
AND  pvsa.TOLERANCE_ID = att.TOLERANCE_ID(+)
--AND pvsa.PAY_DATE_BASIS_LOOKUP_CODE='DISCOUNT'
Order by pv.segment1 asc

PO Supplier Extract Query

SELECT   pv.vendor_id,
                   pvs.org_id,
                   pvs.vendor_site_id,
                   pv.VENDOR_NAME,
                   pv.segment1 SUPPLIER_NUMBER,
                   pvs.VENDOR_SITE_CODE SITE_NAME,
                   hou.name OPEARTING_UNIT,
                   pvs.ADDRESS_LINE1,
                   pvs.ADDRESS_LINE2,
                   pvs.ADDRESS_LINE3,
                   pvs.CITY,
                   pvs.STATE,
                   pvs.ZIP,
                   pvs.PROVINCE,
                   pvs.COUNTRY,
                   pv.START_DATE_ACTIVE,
                   pv.end_DATE_ACTIVE,
                   pvs.purchasing_site_flag MANAGE_SITE_PUR,
                   pvs.rfq_only_site_flag MANAGE_SITE_RFQ,
                   pvs.pay_site_flag MANAGE_SITE_PAY,
                   qrslt.pay_flag ADDRESS_PURPOSE_PAY_FLAG,
                   qrslt.pur_flag ADDRESS_PURPOSE_PUR_FLAG,
                   qrslt.rfq_flag ADDRESS_PURPOSE_RFQ_FLAG,
                   fl.MEANING HEADER_PAYMENT_METHOD,
                   pv.INVOICE_CURRENCY_CODE HEADER_INVOICE_CURRENCY_CODE,
                   pv.PAYMENT_CURRENCY_CODE HEADER_PAYMENT_CURRENCY_CODE,
                   (SELECT   name
                      FROM   ap_terms
                     WHERE   term_id = pv.terms_id)
                      HEADER_TERM_NAME,
                   fl.MEANING SITE_PAYMENT_METHOD,
                   pvs.INVOICE_CURRENCY_CODE SITE_INVOICE_CURRENCY_CODE,
                   pvs.PAYMENT_CURRENCY_CODE SITE_PAYMENT_CURRENCY_CODE,
                   (SELECT   name
                      FROM   ap_terms
                     WHERE   term_id = pvs.terms_id)
                      SITE_TERM_NAME
            --INACTIVE_DATE
            FROM   ap_suppliers pv,
                   ap_supplier_sites_all pvs,
                   hr_operating_units hou,
                   fnd_lookups fl,
                   fnd_lookups fl1,
                   (SELECT   hps.party_site_id,
                             hps.party_site_name,
                             DECODE (pay.site_use_type, NULL, 'N', 'Y')
                                AS pay_flag,
                             DECODE (pur.site_use_type, NULL, 'N', 'Y')
                                AS pur_flag,
                             DECODE (rfq.site_use_type, NULL, 'N', 'Y')
                                AS rfq_flag,
                             hps.last_update_date,
                             hps.end_date_active,
                             hps.start_date_active,
                             p_notes.notes,
                             hcp1.phone_area_code,
                             hcp1.phone_number,
                             hcp1.contact_point_id AS phone_contact_id,
                             hcp1.object_version_number
                                AS phone_object_version_number,
                             hcp2.email_address AS hcp_email,
                             hcp2.contact_point_id AS email_contact_id,
                             hcp2.object_version_number
                                AS email_object_version_number,
                             hcp3.object_version_number
                                AS fax_object_version_number,
                             hcp3.phone_area_code AS fax_area_code,
                             hcp3.phone_number AS fax_number,
                             hcp3.contact_point_id AS fax_contact_id,
                             hzl.address1,
                             hzl.address2,
                             hzl.address3,
                             hzl.address4,
                             hzl.city,
                             hzl.state,
                             hzl.province,
                             hzl.county,
                             hzl.country,
                             hzl.postal_plus4_code,
                             hzl.postal_code,
                             hzl.location_id,
                             hps.party_id AS party_id,
                             hps.status AS status
                      FROM   hz_party_sites hps,
                             hz_party_site_uses pay,
                             hz_party_site_uses pur,
                             hz_party_site_uses rfq,
                             pos_address_notes p_notes,
                             hz_contact_points hcp1,
                             hz_contact_points hcp2,
                             hz_contact_points hcp3,
                             hz_locations hzl
                     WHERE   hps.location_id = hzl.location_id
                             AND NVL (hps.end_date_active, SYSDATE) >=
                                   SYSDATE
                             AND pay.party_site_id(+) = hps.party_site_id
                             AND pur.party_site_id(+) = hps.party_site_id
                             AND rfq.party_site_id(+) = hps.party_site_id
                             -- and hps.party_site_id=1488182
                             AND p_notes.party_site_id(+) = hps.party_site_id
                             AND pay.status(+) = 'A'
                             AND pur.status(+) = 'A'
                             AND rfq.status(+) = 'A'
                             AND NVL (pay.end_date(+), SYSDATE) >= SYSDATE
                             AND NVL (pur.end_date(+), SYSDATE) >= SYSDATE
                             AND NVL (rfq.end_date(+), SYSDATE) >= SYSDATE
                             AND NVL (pay.begin_date(+), SYSDATE) <= SYSDATE
                             AND NVL (pur.begin_date(+), SYSDATE) <= SYSDATE
                             AND NVL (rfq.begin_date(+), SYSDATE) <= SYSDATE
                             AND pay.site_use_type(+) = 'PAY'
                             AND pur.site_use_type(+) = 'PURCHASING'
                             AND rfq.site_use_type(+) = 'RFQ'
                             AND hcp1.owner_table_id(+) = hps.party_site_id
                             AND hcp1.CONTACT_POINT_TYPE(+) = 'PHONE'
                             AND hcp1.phone_line_type(+) = 'GEN'
                             AND hcp1.status(+) = 'A'
                             AND hcp1.owner_table_name(+) = 'HZ_PARTY_SITES'
                             AND hcp1.primary_flag(+) = 'Y'
                             AND hcp2.owner_table_id(+) = hps.party_site_id
                             AND hcp2.CONTACT_POINT_TYPE(+) = 'EMAIL'
                             AND hcp2.status(+) = 'A'
                             AND hcp2.owner_table_name(+) = 'HZ_PARTY_SITES'
                             AND hcp2.primary_flag(+) = 'Y'
                             AND hcp3.owner_table_id(+) = hps.party_site_id
                             AND hcp3.CONTACT_POINT_TYPE(+) = 'PHONE'
                             AND hcp3.phone_line_type(+) = 'FAX'
                             AND hcp3.status(+) = 'A'
                             AND hcp3.owner_table_name(+) = 'HZ_PARTY_SITES')
                   QRSLT
           WHERE       pv.vendor_id = pvs.vendor_id
                   AND QRSLT.party_site_id = pvs.party_site_id
                   AND pvs.org_id = hou.organization_id
                   AND fl.LOOKUP_TYPE = 'OKL_AP_PAYMENT_METHOD'
                   AND pv.PAYMENT_METHOD_LOOKUP_CODE = fl.LOOKUP_CODE(+)
                   AND fl1.LOOKUP_TYPE = 'OKL_AP_PAYMENT_METHOD'
                   AND pvs.PAYMENT_METHOD_LOOKUP_CODE = fl1.LOOKUP_CODE(+)
                   AND NVL (pv.end_DATE_ACTIVE, SYSDATE) >= SYSDATE--and pv.end_DATE_ACTIVE <= nvl(sysdate, pv.end_DATE_ACTIVE)
--  AND pv.SEGMENT1 = '156336'
--and pvs.org_id =111
        

API to update po_vendor_sites_all (ap_vendor_sites_pkg.update_row)

/* Formatted on 05-05-2015 19:35:48 (QP5 v5.114.809.3010) */
DECLARE
   CURSOR cur_suppsites_r11 (
      p_org_id                 NUMBER
   )
   IS
      SELECT   pvss.LIABILITY_ACCOUNT,
               pvss.PREPAYMENT_ACCOUNT,
               pv.segment1 SUPPLIER_NUMBER,
               pv.VENDOR_NAME,
               pvsa.*,
               pvsa.ROWID
        FROM   po_vendors pv,
               po_vendor_sites_all pvsa,
               po_vendor_sites_stg pvss
       WHERE       1 = 1
               AND pv.vendor_id = pvsa.vendor_id
               AND pvsa.VENDOR_SITE_CODE = pvss.VENDOR_SITE_CODE
               AND pvsa.org_id = pvss.org_id
               AND pv.segment1 = pvss.SUPPLIER_NUMBER
          --     AND pv.VENDOR_NAME = pvss.VENDOR_NAME
         --  and  pvss.VENDOR_SITE_CODE ='LOUISVILLE104'
               AND process_flag ='P'
               AND pvss.org_id = p_org_id;

   v_date                 DATE;
   v_itemkey              VARCHAR2 (100);
   v_vendor_site_id       NUMBER;
   v_user_id              NUMBER;
   v_resp_id              NUMBER;
   v_org_id               number;
   V_LIABILITY_ACCOUNT    number;
   V_PREPAYMENT_ACCOUNT   number;
   l_cnt number :=0;
BEGIN
   DBMS_OUTPUT.put_line ('program start');
   /* v_user_id := 7240;                         --fnd_profile.VALUE ('USER_ID');
    v_resp_id := 51683;                        --fnd_profile.VALUE ('RESP_ID');
    v_vendor_site_id := 37425;
    fnd_global.apps_initialize (user_id        => v_user_id,
                                resp_id        => v_resp_id,
                                resp_appl_id   => 200);*/

   v_org_id := 103;
   DBMS_OUTPUT.put_line ('v_org_id' || v_org_id);

   BEGIN
      DBMS_APPLICATION_INFO.set_client_info (v_org_id);
   END;



   FOR v_site IN cur_suppsites_r11 (v_org_id)
   LOOP
   l_cnt := l_cnt+1;
      DBMS_OUTPUT.put_line ('loop start' ||l_cnt);
       DBMS_OUTPUT.put_line ('cur_suppsites_r11.SUPPLIER_NUMBER' ||v_site.SUPPLIER_NUMBER ||'-'||v_site.VENDOR_SITE_CODE);

        DBMS_OUTPUT.put_line ('v_site.LIABILITY_ACCOUNT' ||v_site.LIABILITY_ACCOUNT);

      IF v_site.LIABILITY_ACCOUNT IS NOT NULL
      THEN
         BEGIN
            SELECT   gcc.CODE_COMBINATION_ID                          --,gcc.*
              INTO   V_LIABILITY_ACCOUNT
              FROM   gl_code_combinations gcc,
                     gl_sets_of_books sob,
                     hr_operating_units hou
             WHERE          gcc.segment1
                         || '.'
                         || gcc.segment2
                         || '.'
                         || gcc.segment3
                         || '.'
                         || gcc.segment4
                         || '.'
                         || gcc.segment5
                         || '.'
                         || gcc.segment6
                         || '.'
                         || gcc.segment7
                         || '.'
                         || gcc.segment8 = v_site.LIABILITY_ACCOUNT
                     AND gcc.CHART_OF_ACCOUNTS_ID = sob.CHART_OF_ACCOUNTS_ID
                     AND sob.SET_OF_BOOKS_ID = hou.SET_OF_BOOKS_ID
                     AND hou.ORGANIZATION_ID = v_org_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               V_LIABILITY_ACCOUNT := NULL;
         END;
      ELSE
         V_LIABILITY_ACCOUNT := NULL;
      END IF;

      DBMS_OUTPUT.put_line ('V_LIABILITY_ACCOUNT' || V_LIABILITY_ACCOUNT);

    DBMS_OUTPUT.put_line ('v_site.PREPAYMENT_ACCOUNT' ||v_site.PREPAYMENT_ACCOUNT);
      IF v_site.PREPAYMENT_ACCOUNT IS NOT NULL
      THEN
         BEGIN
            SELECT   gcc.CODE_COMBINATION_ID                          --,gcc.*
              INTO   V_PREPAYMENT_ACCOUNT
              FROM   gl_code_combinations gcc,
                     gl_sets_of_books sob,
                     hr_operating_units hou
             WHERE          gcc.segment1
                         || '.'
                         || gcc.segment2
                         || '.'
                         || gcc.segment3
                         || '.'
                         || gcc.segment4
                         || '.'
                         || gcc.segment5
                         || '.'
                         || gcc.segment6
                         || '.'
                         || gcc.segment7
                         || '.'
                         || gcc.segment8 = v_site.PREPAYMENT_ACCOUNT
                     AND gcc.CHART_OF_ACCOUNTS_ID = sob.CHART_OF_ACCOUNTS_ID
                     AND sob.SET_OF_BOOKS_ID = hou.SET_OF_BOOKS_ID
                     AND hou.ORGANIZATION_ID = v_org_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               V_PREPAYMENT_ACCOUNT := NULL;
         END;
      ELSE
         V_PREPAYMENT_ACCOUNT := NULL;
      END IF;

      DBMS_OUTPUT.put_line ('V_PREPAYMENT_ACCOUNT' || V_PREPAYMENT_ACCOUNT);

      DBMS_OUTPUT.put_line ('API Start');
  BEGIN
      ap_vendor_sites_pkg.update_row (
         x_rowid                          => v_site.ROWID,
         x_vendor_site_id                 => v_site.vendor_site_id,
         x_last_update_date               => v_site.last_update_date,
         x_last_updated_by                => v_site.last_updated_by,
         x_vendor_id                      => v_site.vendor_id,
         x_vendor_site_code               => v_site.vendor_site_code,
         x_last_update_login              => v_site.last_update_login,
         x_creation_date                  => v_site.creation_date,
         x_created_by                     => v_site.created_by,
         x_purchasing_site_flag           => v_site.purchasing_site_flag,
         x_rfq_only_site_flag             => v_site.rfq_only_site_flag,
         x_pay_site_flag                  => v_site.pay_site_flag,
         x_attention_ar_flag              => v_site.attention_ar_flag,
         x_address_line1                  => v_site.address_line1,
         x_address_line2                  => v_site.address_line2,
         x_address_line3                  => v_site.address_line3,
         x_city                           => v_site.city,
         x_state                          => v_site.state,
         x_zip                            => v_site.zip,
         x_province                       => v_site.province,
         x_country                        => v_site.country,
         x_area_code                      => v_site.area_code,
         x_phone                          => v_site.phone,
         x_customer_num                   => v_site.customer_num,
         x_ship_to_location_id            => v_site.ship_to_location_id,
         x_bill_to_location_id            => v_site.bill_to_location_id,
         x_ship_via_lookup_code           => v_site.ship_via_lookup_code,
         x_freight_terms_lookup_code      => v_site.freight_terms_lookup_code,
         x_fob_lookup_code                => v_site.fob_lookup_code,
         x_inactive_date                  => v_date,
         x_fax                            => v_site.fax,
         x_fax_area_code                  => v_site.fax_area_code,
         x_telex                          => v_site.telex,
         x_payment_method_lookup_code     => v_site.payment_method_lookup_code,
         x_bank_account_name              => v_site.bank_account_name,
         x_bank_account_num               => v_site.bank_account_num,
         x_bank_num                       => v_site.bank_num,
         x_bank_account_type              => v_site.bank_account_type,
         x_terms_date_basis               => v_site.terms_date_basis,
         x_current_catalog_num            => v_site.current_catalog_num,
         x_vat_code                       => v_site.vat_code,
         x_distribution_set_id            => v_site.distribution_set_id,
         x_accts_pay_ccid                 => V_LIABILITY_ACCOUNT,
         --v_site.accts_pay_code_combination_id,
         x_future_dated_payment_ccid      => v_site.future_dated_payment_ccid,
         x_prepay_code_combination_id     => V_PREPAYMENT_ACCOUNT,
         --v_site.prepay_code_combination_id,
         x_pay_group_lookup_code          => v_site.pay_group_lookup_code,
         x_payment_priority               => v_site.payment_priority,
         x_terms_id                       => v_site.terms_id,
         x_invoice_amount_limit           => v_site.invoice_amount_limit,
         x_pay_date_basis_lookup_code     => v_site.pay_date_basis_lookup_code,
         x_always_take_disc_flag          => v_site.always_take_disc_flag,
         x_invoice_currency_code          => v_site.invoice_currency_code,
         x_payment_currency_code          => v_site.payment_currency_code,
         x_hold_all_payments_flag         => v_site.hold_all_payments_flag,
         x_hold_future_payments_flag      => v_site.hold_future_payments_flag,
         x_hold_reason                    => v_site.hold_reason,
         x_hold_unmatched_invoices_flag   => v_site.hold_unmatched_invoices_flag,
         x_match_option                   => v_site.match_option,
         x_create_debit_memo_flag         => v_site.create_debit_memo_flag,
         x_exclusive_payment_flag         => v_site.exclusive_payment_flag,
         x_tax_reporting_site_flag        => v_site.tax_reporting_site_flag,
         x_attribute_category             => v_site.attribute_category,
         x_attribute1                     => v_site.attribute1,
         x_attribute2                     => v_site.attribute2,
         x_attribute3                     => v_site.attribute3,
         x_attribute4                     => v_site.attribute4,
         x_attribute5                     => v_site.attribute5,
         x_attribute6                     => v_site.attribute6,
         x_attribute7                     => v_site.attribute7,
         x_attribute8                     => v_site.attribute8,
         x_attribute9                     => v_site.attribute9,
         x_attribute10                    => v_site.attribute10,
         x_attribute11                    => v_site.attribute11,
         x_attribute12                    => v_site.attribute12,
         x_attribute13                    => v_site.attribute13,
         x_attribute14                    => v_site.attribute14,
         x_attribute15                    => v_site.attribute15,
         x_validation_number              => v_site.validation_number,
         x_exclude_freight_from_disc      => v_site.exclude_freight_from_discount,
         x_vat_registration_num           => v_site.vat_registration_num,
         x_offset_tax_flag                => v_site.offset_tax_flag,
         x_check_digits                   => v_site.check_digits,
         x_bank_number                    => v_site.bank_number,
         x_address_line4                  => v_site.address_line4,
         x_county                         => v_site.county,
         x_address_style                  => v_site.address_style,
         x_language                       => v_site.LANGUAGE,
         x_allow_awt_flag                 => v_site.allow_awt_flag,
         x_awt_group_id                   => v_site.awt_group_id,
         x_pay_on_code                    => v_site.pay_on_code,
         x_default_pay_site_id            => v_site.default_pay_site_id,
         x_pay_on_receipt_summary_code    => v_site.pay_on_receipt_summary_code,
         x_bank_branch_type               => v_site.bank_branch_type,
         x_edi_id_number                  => v_site.edi_id_number,
         x_edi_payment_method             => v_site.edi_payment_method,
         x_edi_payment_format             => v_site.edi_payment_format,
         x_edi_remittance_method          => v_site.edi_remittance_method,
         x_edi_remittance_instruction     => v_site.edi_remittance_instruction,
         x_edi_transaction_handling       => v_site.edi_transaction_handling,
         x_auto_tax_calc_flag             => v_site.auto_tax_calc_flag,
         x_auto_tax_calc_override         => v_site.auto_tax_calc_override,
         x_amount_includes_tax_flag       => v_site.amount_includes_tax_flag,
         x_ap_tax_rounding_rule           => v_site.ap_tax_rounding_rule,
         x_vendor_site_code_alt           => v_site.vendor_site_code_alt,
         x_address_lines_alt              => v_site.address_lines_alt,
         x_global_attribute_category      => v_site.global_attribute_category,
         x_global_attribute1              => v_site.global_attribute1,
         x_global_attribute2              => v_site.global_attribute2,
         x_global_attribute3              => v_site.global_attribute3,
         x_global_attribute4              => v_site.global_attribute4,
         x_global_attribute5              => v_site.global_attribute5,
         x_global_attribute6              => v_site.global_attribute6,
         x_global_attribute7              => v_site.global_attribute7,
         x_global_attribute8              => v_site.global_attribute8,
         x_global_attribute9              => v_site.global_attribute9,
         x_global_attribute10             => v_site.global_attribute10,
         x_global_attribute11             => v_site.global_attribute11,
         x_global_attribute12             => v_site.global_attribute12,
         x_global_attribute13             => v_site.global_attribute13,
         x_global_attribute14             => v_site.global_attribute14,
         x_global_attribute15             => v_site.global_attribute15,
         x_global_attribute16             => v_site.global_attribute16,
         x_global_attribute17             => v_site.global_attribute17,
         x_global_attribute18             => v_site.global_attribute18,
         x_global_attribute19             => v_site.global_attribute19,
         x_global_attribute20             => v_site.global_attribute20,
         x_bank_charge_bearer             => v_site.bank_charge_bearer,
         x_ece_tp_location_code           => v_site.ece_tp_location_code,
         x_pcard_site_flag                => v_site.pcard_site_flag,
         x_country_of_origin_code         => v_site.country_of_origin_code,
         x_calling_sequence               => NULL,
         x_shipping_location_id           => NULL,
         x_supplier_notif_method          => v_site.supplier_notif_method,
         x_email_address                  => v_site.email_address,
         x_remittance_email               => v_site.remittance_email,
         x_primary_pay_site_flag          => v_site.primary_pay_site_flag,
         x_shipping_control               => v_site.shipping_control,
         x_gapless_inv_num_flag           => v_site.gapless_inv_num_flag,
         x_selling_company_identifier     => v_site.selling_company_identifier,
         x_duns_number                    => v_site.duns_number,
         x_tolerance_id                   => v_site.tolerance_id
      );
      DBMS_OUTPUT.put_line ('API End');
   
         UPDATE   po_vendor_sites_stg
            SET   process_flag = 'S'
          WHERE       SUPPLIER_NUMBER = v_site.SUPPLIER_NUMBER
               --   AND VENDOR_NAME = v_site.VENDOR_NAME
                  AND VENDOR_SITE_CODE = v_site.VENDOR_SITE_CODE
                  AND org_id = v_site.org_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            UPDATE   po_vendor_sites_stg
               SET   process_flag = 'E'
                 --   ERROR_MESSAGE =SQLERRM
             WHERE       SUPPLIER_NUMBER = v_site.SUPPLIER_NUMBER
                   --  AND VENDOR_NAME = v_site.VENDOR_NAME
                     AND VENDOR_SITE_CODE = v_site.VENDOR_SITE_CODE
                     AND org_id = v_site.org_id;
      END;
      COMMIT;
      DBMS_OUTPUT.put_line ('After commit');
   END LOOP;

   DBMS_OUTPUT.put_line ('Loop End');
EXCEPTION
   WHEN OTHERS
   THEN  
      DBMS_OUTPUT.put_line ('other exception' || SQLERRM); 
END;
/