GL Table Changes R12 in Oracle APPS

SL_NO	11i TABLES	R12 TABLES
1	GL_ACCOUNT_HIERARCHIES	GL_ACCOUNT_HIERARCHIES
2	GL_AHE_DETAIL_RANGES_GT	GL_AHE_DETAIL_RANGES_GT
3	GL_ALLOC_BATCHES	GL_ALLOC_BATCHES
4	GL_ALLOC_FORMULA_LINES	GL_ALLOC_FORMULA_LINES
5	GL_ALLOC_FORMULAS	GL_ALLOC_FORMULAS
6	GL_ALLOC_HISTORY	GL_ALLOC_HISTORY
7	GL_ALLOC_SEGVALS_INT
8	GL_APPLICATION_GROUPS
9	GL_ARCHIVE_HISTORY	GL_ARCHIVE_HISTORY
10	GL_AUTHORIZATION_LIMITS	GL_AUTHORIZATION_LIMITS
11	GL_AUTO_ALLOC_BAT_HIST_DET	GL_AUTO_ALLOC_BAT_HIST_DET
12	GL_AUTO_ALLOC_BATCH_HISTORY	GL_AUTO_ALLOC_BATCH_HISTORY
13	GL_AUTO_ALLOC_BATCHES	GL_AUTO_ALLOC_BATCHES
14	GL_AUTO_ALLOC_REV_BATCHES	GL_AUTO_ALLOC_REV_BATCHES
15	GL_AUTO_ALLOC_SET_HISTORY	GL_AUTO_ALLOC_SET_HISTORY
16	GL_AUTO_ALLOC_SETS	GL_AUTO_ALLOC_SETS
17	GL_AUTOMATIC_POSTING_OPTIONS	GL_AUTOMATIC_POSTING_OPTIONS
18	GL_AUTOMATIC_POSTING_SETS	GL_AUTOMATIC_POSTING_SETS
19	GL_AUTOREVERSE_OPTIONS	GL_AUTOREVERSE_OPTIONS
20	GL_BALANCES	GL_BALANCES
21	GL_BALANCES_DELTA	GL_BALANCES_DELTA
22	GL_BALANCES_DELTA_INTERIM	GL_BALANCES_DELTA_INTERIM
23	GL_BC_DUAL	GL_BC_DUAL
24	GL_BC_DUAL2	GL_BC_DUAL2
25	GL_BC_EVENT_TIMESTAMPS	GL_BC_EVENT_TIMESTAMPS
26	GL_BC_OPTION_DETAILS	GL_BC_OPTION_DETAILS
27	GL_BC_OPTIONS	GL_BC_OPTIONS
28	GL_BC_PACKET_ARRIVAL_ORDER	GL_BC_PACKET_ARRIVAL_ORDER
29	GL_BC_PACKETS	GL_BC_PACKETS
30	GL_BC_PERIOD_MAP	GL_BC_PERIOD_MAP
31	GL_BIS_SEGVAL_INT	GL_BIS_SEGVAL_INT
32	GL_BUDGET_ASSIGNMENT_RANGES	GL_BUDGET_ASSIGNMENT_RANGES
33	GL_BUDGET_ASSIGNMENTS	GL_BUDGET_ASSIGNMENTS
34	GL_BUDGET_BATCHES	GL_BUDGET_BATCHES
35	GL_BUDGET_ENTITIES	GL_BUDGET_ENTITIES
36	GL_BUDGET_FROZEN_RANGES	GL_BUDGET_FROZEN_RANGES
37	GL_BUDGET_INTERFACE	GL_BUDGET_INTERFACE
38	GL_BUDGET_INTERIM	GL_BUDGET_INTERIM
39	GL_BUDGET_PERIOD_RANGES	GL_BUDGET_PERIOD_RANGES
40	GL_BUDGET_RANGE_INTERIM	GL_BUDGET_RANGE_INTERIM
41	GL_BUDGET_TYPES	GL_BUDGET_TYPES
42	GL_BUDGET_VERSIONS	GL_BUDGET_VERSIONS
43	GL_BUDGETS	GL_BUDGETS
44	GL_CARRYFORWARD_RANGES	GL_CARRYFORWARD_RANGES
45	GL_CODE_COMBINATIONS	GL_CODE_COMBINATIONS
46	GL_CONCURRENCY_CONTROL	GL_CONCURRENCY_CONTROL
47	GL_CONCURRENT_SCHEDULES	GL_CONCURRENT_SCHEDULES
48	GL_CONS_BATCHES	GL_CONS_BATCHES
49	GL_CONS_FLEX_HIERARCHIES	GL_CONS_FLEX_HIERARCHIES
50	GL_CONS_FLEXFIELD_MAP	GL_CONS_FLEXFIELD_MAP

 

What are the possible reasons for Interface Trip Stop to complete with Error in Oracle APPS?

Below are the few possible reasons for Interface Trip Stop completes with Error:

• Order is on Hold
• Tax is not applied properly
• On-Hand quantity is not available in the Inventory
• Inventory Period is not open
• Make sure Schedule Ship Date or Requested Date is within the range of order Date

What is Interface Trip Stop in Order Management in Oracle APPS?

Interface Trip Stop is triggered at the time of shipping if the option ‘Defer Interface’ is not checked. When this report is run, it performs two main things:

• Update the order management (OE_ORDER_LINES_ALL)
• Trigger the inventory interface (TO UPDATE Inventory tables)

What is Bill of Lading in Order Management in Oracle APPS?

• Bill of Lading is a legal document between the shipper of a particular item and the carrier detailing the type, quantity and destination of the good being carried. 
• The bill of lading also serves as a receipt of shipment when the good is delivered to the predetermined destination. 
• This document must accompany the shipped goods, no matter the form of transportation, and must be signed by an authorized representative from the carrier, shipper and receiver.

What is Pick Slip and Packing Slip in Order Management in Oracle APPS?

Pick Slip: It is a shipping document that the pickers use to locate items in the warehouse/inventory to ship for an order.

Packing Slip: It is a shipping document that is sent along with the shipment which details the contents that are sent in that shipment.

What are Defaulting Rules in Order Management in Oracle APPS?

When creating the sales order, you can define defaulting rules so that the default values of the fields populate automatically instead of typing all information manually.

What is Shipping Exceptions Report in Oracle APPS?

Shipping Exceptions report prints the Exception Messages during Ship Confirmation.

What are Picking Rules in Order Management in Oracle APPS?

• A user-defined set of criteria to define the priorities, Oracle Order Management uses when picking items out of finished goods from inventory to ship to a customer. 
• Picking rules are defined in Oracle Inventory.

At what stage an Order cannot be cancelled?

Sales Order cannot be cancelled once it is Pick Confirmed.

Describe the Order Line Status during the Sales Order Process in Oracle APPS?

What are the different RMA Order Types in Oracle APPS?

• RMA with Credit is used when the customer returns the physical product and also receives credit as a result of the return.
• RMA no Credit is used when the customer will return the product but will not be receiving a credit as a result of the return.
• RMA Credit only is used when the customer will receive a credit, but the physical return of the product is not required.

Describe the Header Status during the Sales Order Process in oracle apps

What are the Different Types of Sales Orders available in Oracle APPS?

• Standard
• Mixed
• Return

Describe the Order Flow in Order to Cash life cycle in Oracle APPS

• Enter the Sales Order 
• Book the Sales Order
• Pick Release
• Ship Confirm
• Auto Invoice Generation
• Creating Receipt
• Transfer to GL

What are the various Order Management Processes in Oracle APPS Order Management

• Standard Orders: This method supports Make to Stock business model where the products are made and kept in stock. The products are then shipped to the customer based on his order.
• Configure to Orders: This method supports Assemble to Order business model. The product configuration is chosen at the time of sales order entry. WIP job is created based on the requirement of the sales order.
• Drop Ship Orders: In Drop Ship Method, your supplier will ship the goods to your customer on your behalf.
• Internal Sales Orders: Internal Sales Orders are created across two different organizations of the same Business group. Internal requisition is raised first and it is converted to an internal sales order where the items are shipped based on the quantity specified in the internal sales order.

API to delete Purchase Orders in Oracle APPS

Note: We had a requirement to delete Purchase Orders which are in INCOMPLETE status during migration. Please check with oracle or your team for your requirement. This is just to give some idea.  Do not execute in production.

DECLARE

l_result  BOOLEAN;

BEGIN

FOR i IN (SELECT po_header_id

,type_lookup_code 

FROM po_headers_all 

       WHERE authorization_status = 'INCOMPLETE' 

AND org_id = 12345)

LOOP

DBMS_OUTPUT.put_line ( 'API PO_HEADERS_SV1 Call to delete PO with header :'||i.po_header_id);

l_result := po_headers_sv1.delete_po (x_po_header_id     => i.po_header_id

,x_type_lookup_code => i.type_lookup_code

,p_skip_validation  => 'N'

);

IF l_result = TRUE THEN

COMMIT;

DBMS_OUTPUT.put_line ( 'PO with header: '|| i.po_header_id||',Deleted Successfully');

ELSE

ROLLBACK;

DBMS_OUTPUT.put_line ( 'PO with header: '|| i.po_header_id||',Failed to Delete');

END IF;

END LOOP;

DBMS_OUTPUT.put_line ( 'Deletion Process Over');

   

EXCEPTION

WHEN OTHERS THEN

    DBMS_OUTPUT.put_line ( 'Error : '|| SQLERRM);

END;

Query to get PO With Approval , Invoice and Payment Details in Oracle APPS

SELECT inv.org_id

      ,supp.segment1 supplier_number

      ,supp.vendor_name supplier_name

      ,UPPER (supp.vendor_type_lookup_code) supplier_type

      ,supp_site.vendor_site_code supplier_site_code

      ,supp_site.address_line1

  ,supp_site.address_line2

  ,supp_site.address_line3

  ,supp_site.address_line4

      ,supp_site.city 

      ,supp_site.country

      ,TO_CHAR (po_hdrs.creation_date, 'DD-MON-RRRR') po_creation_date

      ,po_hdrs.segment1 po_number

      ,po_hdrs.type_lookup_code po_type

      ,po_dist.quantity_ordered

      ,po_dist.quantity_cancelled

      ,po_lines.item_id 

      ,po_lines.item_description item_description

      ,po_lines.unit_price

      ,(NVL (po_dist.quantity_ordered, 0) - NVL (po_dist.quantity_cancelled, 0))

       * NVL (po_lines.unit_price, 0)

          po_line_amount

      ,(SELECT DECODE (ph.approved_flag, 'Y', 'Approved'

                                   , 'R', 'Requires Reapproval'

   , 'F', 'Failed Approval'

   , 'N', 'Never Approved'

   )

          FROM po.po_headers_all ph

         WHERE ph.po_header_id = po_hdrs.po_header_id)

          po_approved_status

      ,inv.invoice_type_lookup_code invoice_type

      ,inv.invoice_amount invoice_amount

      ,TO_CHAR(inv.invoice_date, 'DD-MON-RRRR') invoice_date

      ,inv.invoice_num 

      ,(SELECT DECODE (ap_inv_dist.match_status_flag, 'A', 'Approved'

                                                , 'T', 'Tested but not Validate'

,'Never Validated')

          FROM ap.ap_invoice_distributions_all ap_inv_dist

         WHERE ap_inv_dist.invoice_distribution_id = inv_dist.invoice_distribution_id) invoice_approved_status

      ,inv.amount_paid

      ,inv_pmt.amount

      ,inv_pmt.check_id

      ,inv_pmt.invoice_payment_id payment_id

      ,chk.check_number cheque_number

      ,TO_CHAR(chk.check_date, 'DD-MON-RRRR') check_date

  FROM ap_invoices_all inv

      ,ap_invoice_distributions_all inv_dist

  ,po_distributions_all po_dist

  ,po_headers_all po_hdrs

  ,ap_suppliers supp

  ,ap_supplier_sites_all supp_site

  ,po_lines_all po_lines

  ,ap_invoice_payments_all inv_pmt

      ,ap_checks_all chk

 WHERE     inv.invoice_id = inv_dist.invoice_id

       AND inv_dist.po_distribution_id = po_dist.po_distribution_id(+)

       AND po_dist.po_header_id = po_hdrs.po_header_id(+)

       AND supp.vendor_id(+) = po_hdrs.VENDOR_ID

       AND supp_site.vendor_site_id(+) = po_hdrs.vendor_site_id

       AND po_hdrs.po_header_id = po_lines.po_header_id

       AND po_dist.po_line_id = po_lines.po_line_id

       AND inv.invoice_id = inv_pmt.invoice_id

       AND inv_pmt.check_id = chk.check_id

       AND supp_site.vendor_site_id = chk.vendor_site_id

       AND po_dist.po_header_id IS NOT NULL

       AND inv.payment_status_flag = 'Y'

       AND po_hdrs.type_lookup_code != 'BLANKET'

AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG in Oracle APPS

The Invoice Header form derives the invoice validation Status based on the following:

• Validated: If all of the invoice distributions have a MATCH_STATUS_FLAG = 'A'
• Never Validated: If all of the invoice distributions have a MATCH_STATUS_FLAG = null or 'N'
• Needs Revalidation: 
• If there are any rows in AP_HOLDS that do not have a release code.
• If any of the invoice distributions have a MATCH_STATUS_FLAG = 'T'.
• If the invoice distributions have MATCH_STATUS_FLAG values = 'N', null and 'A'.

Use AP_INVOICES_PKG.GET_APPROVAL_STATUS to find the approval status.

PO_HEADERS_ALL.APPROVED_FLAG in Oracle APPS

SELECT *

  FROM fnd_lookup_values

 WHERE 1 = 1 

      AND lookup_type = 'PO APPROVAL'

      AND enabled_flag = 'Y'

      AND language = USERENV('LANG')

      AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE)

Here lookup_code is APPROVED_FLAG in PO_HEADERS_ALL.

Query to get all Purchase Requisition without a Purchase Order in Oracle APPS

SELECT prh.segment1 requisition_number,

         TO_CHAR(prh.creation_date, 'DD-MON-RRRR') created_on,

         TO_CHAR(prl.creation_date, 'DD-MON-RRRR') line_creation_date,

         prl.line_num,

         msi.segment1 item_code,

         prl.item_description description,

         prl.quantity ,

         TO_CHAR(prl.need_by_date, 'DD-MON-RRRR') required_date,

         requestor.full_name requestor_name,

         buyers.agent_name buyer_name

    FROM po.po_requisition_headers_all prh,

         po.po_requisition_lines_all prl,

         apps.per_people_f requestor,

         (SELECT DISTINCT agent_id, agent_name 

    FROM apps.po_agents_v) buyers,

         po.po_req_distributions_all prd,

         inv.mtl_system_items_b msi,

         po.po_line_locations_all pll,

         po.po_lines_all pl,

         po.po_headers_all ph

   WHERE     prh.requisition_header_id = prl.requisition_header_id

         AND prl.requisition_line_id = prd.requisition_line_id

         AND requestor.person_id = prh.preparer_id

         AND prh.creation_date BETWEEN requestor.effective_start_date

                                   AND requestor.effective_end_date

         AND buyers.agent_id(+) = msi.buyer_id

         AND msi.inventory_item_id = prl.item_id

         AND msi.organization_id = prl.destination_organization_id

         AND pll.line_location_id(+) = prl.line_location_id

         AND pll.po_header_id = ph.po_header_id(+)

         AND pll.po_line_id = pl.po_line_id(+)

         AND prh.authorization_status = 'APPROVED'

         AND pll.line_location_id IS NULL

         AND prl.closed_code IS NULL

         AND NVL (prl.cancel_flag, 'N') <> 'Y'

         AND prh.org_id = 123456

ORDER BY 1, 2

Query to get Cancelled Requisitions in Oracle APPS

SELECT prh.requisition_header_id,

       (SELECT full_name 

           FROM per_all_people_f 

          WHERE person_id = prh.preparer_id

            AND SYSDATE BETWEEN effective_start_date AND effective_end_date) preparer,

       prh.segment1 requisition_num,

       TO_CHAR(TRUNC (prh.creation_date), 'DD-MON-RRRR') creation_date,

       prh.description,

       prh.note_to_authorizer

  FROM apps.po_requisition_headers_all prh

      ,apps.po_action_history pah

 WHERE action_code = 'CANCEL'

   AND pah.object_type_code = 'REQUISITION'

   AND pah.object_id = prh.requisition_header_id

   ORDER BY prh.creation_date

OE_ORDER_LINES_ALL.source_document_type_id in Oracle APPS

SELECT order_source_id, name, description 

FROM oe_order_sources

Here order_source_id will be source_document_type_id  in OE_ORDER_LINES_ALL table.

Query to get all Internal Requisitions that do not have an associated Internal Sales Order

  SELECT rqh.segment1 req_num,

         rql.line_num,

         rql.requisition_header_id,

         rql.requisition_line_id,

         rql.item_id,

         rql.unit_meas_lookup_code,

         rql.unit_price,

         rql.quantity,

         rql.quantity_cancelled,

         rql.quantity_delivered,

         rql.cancel_flag,

         rql.source_type_code,

         rql.source_organization_id,

         rql.destination_organization_id,

         rqh.transferred_to_oe_flag

    FROM po_requisition_lines_all rql

    ,po_requisition_headers_all rqh

   WHERE rql.requisition_header_id = rqh.requisition_header_id

     AND rql.source_type_code = 'INVENTORY'

     AND rql.source_organization_id IS NOT NULL

     AND NOT EXISTS

(SELECT 'Y'--Existing Internal Order

   FROM oe_order_lines_all lin

       ,oe_order_sources order_source

  WHERE lin.source_document_line_id = rql.requisition_line_id

     AND lin.source_document_type_id = order_source.order_source_id

AND order_source.name = 'Internal'

)

ORDER BY rqh.requisition_header_id

        ,rql.line_num;

PO_HEADERS_ALL.TYPE_LOOKUP_CODE in Oracle APPS

SELECT lookup_type, lookup_code, meaning

  FROM fnd_lookup_values

 WHERE     1 = 1

       AND lookup_type = 'POO:DOCUMENT_TYPE'

       AND enabled_flag = 'Y'

       AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE)

                       AND NVL (end_date_active, SYSDATE)

PO_LINE_LOCATIONS_ALL.MATCH_OPTION in Oracle APPS

SELECT lookup_type, lookup_code, meaning

  FROM fnd_lookup_values

 WHERE     1 = 1

       AND lookup_type = 'POS_INVOICE_MATCH_OPTION'

       AND enabled_flag = 'Y'

       AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE)

                       AND NVL (end_date_active, SYSDATE)

Query to extract Open PO's in Oracle APPS

SELECT pha.segment1 po_number

      ,pha.revision_num

      ,comments description

      ,pha.type_lookup_code po_type

      ,TO_CHAR(pha.creation_date, 'DD-MON-RRRR') order_date

      ,pv.vendor_name vendor_name

      ,pv.segment1 vendor_number

      ,pvs.vendor_site_code supplier_site

      ,pha.currency_code

      ,pha.rate_type currency_rate_type

      ,TO_CHAR(pha.rate_date, 'DD-MON-RRRR') currency_rate_date

      ,pha.rate currency_rate

  ,(SELECT SUM(unit_price * quantity) 

      FROM po_lines_all pla

     WHERE pla.po_header_id = pha.po_header_id

   AND pha.org_id = pla.org_id

       AND NVL(pla.closed_code, 'OPEN') <> 'FINALLY CLOSED'

  ) po_header_amount

  ,(SELECT SUM(aiDa.amount)

  FROM po_headers_all pha1

  ,po_distributions_all pda

  ,ap_invoice_distributions_all aida

  ,ap_invoices_all aia

WHERE 1=1

  AND pha1.po_header_id = pha.po_header_id

  AND  pha.po_header_id =  pda.po_header_id

  AND pda.po_distribution_id = aida.po_distribution_id

  AND PDA.PO_LINE_ID = lines.PO_LINE_ID

  AND pda.po_distribution_id = po_dist.po_distribution_id

  AND pda.org_id =aida.org_id

  AND aida.invoice_id = aia.invoice_id) matched_amount

  ,pap.full_name agent_name

      ,pap.employee_number buyer_number

      ,NVL (PHA.CLOSED_CODE, 'OPEN') closure_status    

      ,att.name payment_term

  ,(SELECT location_code 

  FROM hr_locations

WHERE location_id = pha.ship_to_location_id) ship_to_location    

  ,(SELECT location_code 

  FROM hr_locations

WHERE location_id = pha.bill_to_location_id) bill_to_location

      ,lines.line_num

      ,(SELECT line_type 

      FROM po_line_types 

WHERE line_type_id = lines.line_type_id) line_type

      ,(SELECT segment1 

      FROM mtl_system_items_b 

WHERE inventory_item_id = lines.item_id 

   AND organization_id = line_loc.ship_to_organization_id) item

      ,lines.item_id

      ,REPLACE(TRIM(lines.item_description), CHR(10), '')  existing_line_description

      ,lines.quantity

      ,lines.unit_meas_lookup_code uom

      ,lines.unit_price

      ,(lines.quantity * lines.unit_price) price

      ,line_loc.shipment_num

      ,(SELECT organization_code 

      FROM org_organization_definitions 

WHERE organization_id  = line_loc.ship_to_organization_id) ship_to_organization_code

      ,(SELECT location_code 

      FROM hr_locations 

WHERE location_id = line_loc.ship_to_location_id) shipment_ship_to_location

      ,line_loc.ship_to_location_id

      ,line_loc.ship_to_organization_id

      ,TO_CHAR(line_loc.need_by_date, 'DD-MON-RRRR') need_by_date

      ,TO_CHAR(line_loc.PROMISED_DATE, 'DD-MON-RRRR') PROMISED_DATE

      ,line_loc.quantity shipment_quantity

      ,line_loc.QUANTITY_RECEIVED shipment_quantity_received

      ,(SELECT meaning 

      FROM fnd_lookup_values 

         WHERE 1=1

           AND lookup_code = line_loc.match_option

           AND lookup_type LIKE '%POS%INVOICE%MATCH%' 

   AND enabled_flag = 'Y' AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE)

           ) match_option

  ,line_loc.receipt_required_flag

  ,line_loc.inspection_required_flag

  ,line_loc.accrue_on_receipt_flag

  ,line_loc.receiving_routing_id

  ,line_loc.unit_meas_lookup_code

      ,po_dist.distribution_num

      ,(SELECT location_code 

      FROM hr_locations 

WHERE location_id = po_dist.deliver_to_location_id) deliver_to_location

      ,po_dist.destination_type_code

      ,po_dist.quantity_ordered

      ,po_dist.destination_subinventory

      ,(SELECT full_name 

      FROM per_all_people_f 

WHERE person_id = deliver_to_person_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date) deliver_to_person_name

      ,(SELECT employee_number 

      FROM per_all_people_f 

WHERE person_id = deliver_to_person_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date) deliver_to_person_number

      ,(SELECT employee_number 

      FROM per_all_people_f 

WHERE person_id = deliver_to_person_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date) deliver_to_person_id

      ,code_comb.segment1   business_unit

      ,code_comb.segment2   department

      ,code_comb.segment3   account

      ,code_comb.segment4   product

      ,code_comb.segment5   project

      ,code_comb.segment6   property 

      ,code_comb.segment7   spare

      ,code_comb.concatenated_segments

  FROM apps.po_headers_all pha

  ,apps.hr_operating_units hou

  --,fnd_lookup_values po_type

  ,apps.per_all_people_f pap

  ,apps.ap_suppliers pv

  ,apps.ap_supplier_sites_all pvs

  ,apps.ap_terms_tl att

  ,po_lines_all lines

  ,po_line_locations_all line_loc

  ,po_distributions_all po_dist

  ,gl_code_combinations_kfv code_comb

 WHERE     1 = 1

   AND pha.approved_flag = 'Y'

   AND NVL (pha.closed_code, 'OPEN')= 'OPEN'

   AND NVL (pha.cancel_flag, 'N') = 'N'

   AND NVL(lines.cancel_flag, 'N') = 'N'

   AND NVL(line_loc.cancel_flag, 'N') = 'N'

   --AND pha.type_lookup_code IN ('STANDARD')

   AND pha.org_id = hou.organization_id

   --AND pha.type_lookup_code = po_type.lookup_code

   --AND po_type.lookup_type = 'POO:DOCUMENT_TYPE'

   AND pha.agent_id = pap.person_id

   AND pha.vendor_id = pv.vendor_id(+)

   AND pha.vendor_site_id = pvs.vendor_site_id(+)

   AND pha.terms_id = att.term_id(+)

   AND ATT.language = USERENV ('LANG')      

   AND pha.po_header_id = lines.po_header_id

   AND pha.org_id = lines.org_id

   AND line_loc.po_line_id = lines.po_line_id

   AND line_loc.po_header_id = pha.po_header_id

   AND po_dist.po_header_id = pha.po_header_id

   AND po_dist.po_line_id = lines.po_line_id

   AND po_dist.line_location_id = line_loc.line_location_id

   AND po_dist.org_id = line_loc.org_id

   AND code_comb.code_combination_id = po_dist.code_combination_id

   AND pha.org_id = :p_org_id

   AND SYSDATE BETWEEN effective_start_Date AND effective_end_date

   --AND pha.segment1 = '20'

ORDER BY pha.segment1, lines.line_num

Query to Extract Open AP Invoice Lines in Oracle APPS

Lines Data Extraction Query:

--------------------------------------------

SELECT  (SELECT name from hr_operating_units where organization_id = 1234) operating_unit

     , aps.vendor_name

     , apss.vendor_site_code

     , aia.invoice_id

     , aia.invoice_num      

     , aia.invoice_currency_code 

     , aia.payment_currency_code

     , aia.source 

     , flv.meaning invoice_type

     , aia.payment_method_lookup_code payment_method

     , aia.pay_group_lookup_code

     , aia.invoice_amount

     , nvl(aia.invoice_amount,0)  - nvl( aia.amount_paid,0) balance_amount

     , ail.line_number

     , ail.line_type_lookup_code

     , ail.description

     , ail.line_source

     , aid.amount

     , ail.tax_rate_code

     , gcc.concatenated_segments expense_account

     , ail.assets_tracking_flag

     , (select distinct pha.segment1

        from po_distributions_all pda,

                po_headers_all pha

        where pda.po_distribution_id = aid.po_distribution_id 

           and pda.po_header_id = pha.po_header_id) po_number

         , (select distinct pla.line_num

        from po_distributions_all pda,

                po_headers_all pha,

                po_lines_all pla

        where pda.po_distribution_id = aid.po_distribution_id 

           and pla.po_line_id = pda.po_line_id

           and pda.po_header_id = pha.po_header_id) po_line_no

           ,aid.amount_includes_tax_flag

           --,aid.*

  FROM ap_invoices_all aia

     , ap_invoice_lines_all ail

     , ap_invoice_distributions_all aid

     , ap_suppliers aps

     , ap_supplier_sites_all apss

     , hr_operating_units hou

     , fnd_lookup_values flv

     , gl_code_combinations_kfv gcc

WHERE aps.vendor_id = aia.vendor_id

  AND aia.vendor_id = apss.vendor_id

  AND aia.invoice_id = ail.invoice_id

  AND ail.invoice_id = aid.invoice_id 

  AND ail.line_number= aid.invoice_line_number

  AND aia.vendor_site_id = apss.vendor_site_id

  AND hou.organization_id = aia.org_id

  AND aia.cancelled_date IS NULL

  AND ap_invoices_pkg.get_posting_status(aia.invoice_id) in ('Y','P')

--  AND hou.name ='XX Operating'

  AND flv.lookup_type='INVOICE TYPE'

  AND flv.lookup_code = aia.invoice_type_lookup_code

  AND aid.dist_code_combination_id = gcc.code_combination_id(+)

  AND nvl(aid.reversal_flag ,'N') = 'N'

  AND nvl(ail.discarded_flag ,'N') = 'N'

  AND nvl(aid.amount,0) <> 0

--  AND to_date(to_char(aia.gl_date,'DD-MON-YYYY') ,'DD-MON-YYYY') >= to_date('01-JAN-2010', 'DD-MON-YYYY')

AND hou.organization_id = 1

and nvl(aia.invoice_amount,0)  - nvl( aia.amount_paid,0) <> 0

and aia.WFAPPROVAL_STATUS <> 'REJECTED'

ORDER BY aps.vendor_name,apss.vendor_site_code,aia.invoice_date,aia.invoice_num,ail.line_number

Refer Open AP Invoice Headers Data Extraction Query

Query to Extract Open AP Invoices in Oracle APPS

Headers Data Extraction Query:

-----------------------------------------

SELECT --hou.name operating_unit

     (SELECT name from hr_operating_units where organization_id = 12345) operating_unit

     ,aps.vendor_name

     ,aps.segment1 vendor_num

     ,apss.vendor_site_code

     ,aia.invoice_id

     ,aia.invoice_num      

     ,aia.invoice_currency_code 

     ,aia.payment_currency_code

     ,to_char(aia.invoice_date,'DD-MON-YYYY') invoice_date

     ,to_char(aia.gl_date,'DD-MON-YYYY') gl_date

     ,aia.source 

     ,aia.description

     ,flv.meaning invoice_type

     ,at.name term_name

     ,to_char(aia.terms_date,'DD-MON-YYYY') terms_date

     ,aia.payment_status_flag

     ,aia.exchange_rate_type

     ,to_char(aia.exchange_date,'DD-MON-YYYY') exchange_date

     ,aia.exchange_rate

     ,aia.payment_method_code payment_method

     ,aia.pay_group_lookup_code

     ,gcc.concatenated_segments liability_account

     ,aia.invoice_amount

     ,nvl(aia.invoice_amount,0) - nvl( aia.amount_paid,0) balance_amount

     ,decode(nvl(aia.payment_status_flag,'N'),'P','Partially Paid','N','Not Paid','Y','Fully Paid') payment_status

     ,decode(nvl(ap_invoices_pkg.get_posting_status(aia.invoice_id),'N'),'P','Partially Accounted','N','Not Accounted','Y','Fully Accounted') accounting_status

     ,(select distinct pha.segment1

       from po_distributions_all pda,

            po_headers_all pha

           ,ap_invoice_distributions_all aid

      where pda.po_distribution_id = aid.po_distribution_id 

        and pda.po_header_id = pha.po_header_id

        and aia.invoice_id = aid.invoice_id) po_number

  FROM ap_invoices_all aia

     , ap_suppliers aps

     , ap_supplier_sites_all apss

     , hr_operating_units hou

     , fnd_lookup_values flv

     , ap_terms at

     , gl_code_combinations_kfv gcc

WHERE aps.vendor_id = aia.vendor_id

  AND aia.vendor_id = apss.vendor_id

  AND aia.vendor_site_id = apss.vendor_site_id

  AND hou.organization_id = aia.org_id

  AND aia.cancelled_date is null

--  AND hou.name ='XX Operating'

  AND flv.lookup_type ='INVOICE TYPE'

  AND flv.lookup_code = aia.invoice_type_lookup_code

  AND ap_invoices_pkg.get_posting_status(invoice_id) in ('Y','P')

  AND aia.terms_id = at.term_id(+)

--  AND to_date(to_char(aia.gl_date,'DD-MON-YYYY') ,'DD-MON-YYYY') >= to_date('01-JAN-2010', 'DD-MON-YYYY')

  AND aia.accts_pay_code_combination_id = gcc.code_combination_id(+)

  AND hou.organization_id = 1

  and nvl(aia.invoice_amount,0) - nvl( aia.amount_paid,0) <> 0

--  and aia.invoice_num = 'INV_NUM_1'

  and aia.WFAPPROVAL_STATUS <> 'REJECTED'

ORDER BY aps.vendor_name,apss.vendor_site_code,aia.invoice_date

Refer AP Invoice Lines Data Extraction Query

Query to get User Roles in Oracle APPS

SELECT DISTINCT fat.application_name application_name

        ,fu.user_name user_name

        ,CASE

            WHEN NVL (fu.end_date, SYSDATE + 1) > SYSDATE 

                THEN 'ACTIVE'

            ELSE 'INACTIVE'

            END user_status

        ,frt.responsibility_name

        ,furgd.start_date responsibility_start_date

        ,furgd.end_date responsibility_end_date

        ,fu.last_logon_date

        ,fu.end_date user_end_date

        ,fr.end_date

    FROM apps.fnd_user_resp_groups_direct furgd

        ,apps.fnd_user fu

        ,apps.fnd_responsibility_tl frt

        ,apps.fnd_responsibility fr

        ,apps.fnd_application_tl fat

        ,apps.fnd_application fa

   WHERE furgd.user_id = fu.user_id

     AND furgd.responsibility_id = frt.responsibility_id

     AND fr.responsibility_id = frt.responsibility_id

     AND fa.application_id = fat.application_id

     AND fr.application_id = fat.application_id

     AND frt.responsibility_name LIKE 'XX%'

     AND frt.LANGUAGE = USERENV ('LANG')

     AND fat.LANGUAGE = USERENV ('LANG')

     AND NVL(furgd.end_date, SYSDATE)>=SYSDATE

     AND NVL(fu.end_date, SYSDATE)>=SYSDATE

     AND NVL(fr.end_date, SYSDATE)>=SYSDATE

ORDER BY fu.user_name, furgd.end_date

API to update Supplier Site Payment Method Lookup Code in Oracle APPS - iby_disbursement_setup_pub.update_external_payee

Note: 

• Use iby_disbursement_setup_pub.update_external_payee API to update Supplier Site Payment Method. 
• This API will not update ap_supplier_sites_all.payment_method_lookup_code column.
• Supplier Site payment method details are stored in iby_ext_party_pmt_mthds table.
• AP_VENDOR_PUB_PKG.Update_Vendor_Site do not use for Supplier Site Payment Method Update.
• Parameter Exclusive Payment flag(exclusive_pay_flag) is required.

DECLARE

    p_external_payee_tab_type   iby_disbursement_setup_pub.external_payee_tab_type;

    p_ext_payee_id_tab_type     iby_disbursement_setup_pub.ext_payee_id_tab_type;

    p_ext_payee_id_rec          iby_disbursement_setup_pub.Ext_Payee_ID_Rec_Type;

    l_ext_payee_rec             IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Rec_Type;

x_return_status             VARCHAR2 (200) := NULL;

    x_msg_count                 NUMBER := 0;

    x_msg_data                  VARCHAR2 (200) := NULL;

    l_payee_upd_status          iby_disbursement_setup_pub.ext_payee_update_tab_type;

    

i                           NUMBER := 0;

    l_user_id fnd_user.user_id%TYPE;

l_resp_id fnd_responsibility_tl.responsibility_id%TYPE;

l_resp_appl_id fnd_responsibility_tl.application_id%TYPE;

l_org_id hr_operating_units.organization_id%TYPE;

 BEGIN

BEGIN

SELECT user_id

  INTO l_user_id

  FROM fnd_user

WHERE user_name = 'XX_USER';

EXCEPTION

WHEN OTHERS THEN

l_user_id := NULL;

END;

BEGIN

SELECT responsibility_id, application_id 

  INTO l_resp_id, l_resp_appl_id

  FROM fnd_responsibility_tl

WHERE responsibility_name = 'Payables Manager'

   AND language = USERENV('LANG')

   ;

EXCEPTION

WHEN OTHERS THEN

l_resp_id := NULL;

l_resp_appl_id := NULL;

END;

BEGIN

SELECT organization_id

  INTO l_org_id

  FROM hr_operating_units

WHERE name = 'XX Operating Unit';

EXCEPTION

WHEN OTHERS THEN

l_org_id := NULL;

END;

-- Initialize apps session

fnd_global.apps_initialize(user_id      => l_user_id

                          ,resp_id      => l_resp_id

  ,resp_appl_id => l_resp_appl_id

  );

mo_global.init('SQLAP');

fnd_client_info.set_org_context(l_org_id);

    FOR j IN (SELECT ieppm.payment_method_code

                    ,iepa.payee_party_id

                    ,assa.vendor_site_id

                    ,iepa.ext_payee_id

                    ,assa.org_id

                    ,iepa.supplier_site_id

                    ,assa.party_site_id

               FROM ap_supplier_sites_all assa

                   ,ap_suppliers sup

                   ,iby_external_payees_all iepa

                   ,iby_ext_party_pmt_mthds ieppm

                   ,hr_operating_units ou

              WHERE sup.vendor_id = assa.vendor_id

                AND assa.pay_site_flag = 'Y'

                AND assa.vendor_site_id = iepa.supplier_site_id

                AND iepa.ext_payee_id = ieppm.ext_pmt_party_id(+)

                AND ieppm.payment_method_code IS NULL

                AND assa.org_id = ou.organization_id

                AND assa.vendor_site_id = 1024

        )

    LOOP

    

       p_external_payee_tab_type(i).default_pmt_method := 'EFT';

       p_external_payee_tab_type(i).payment_function := 'PAYABLES_DISB';

       p_external_payee_tab_type(i).exclusive_pay_flag := 'N';

       p_external_payee_tab_type(i).payee_party_id := j.payee_party_id;

       p_external_payee_tab_type(i).payer_org_id := j.org_id;

       p_external_payee_tab_type(i).payer_org_type := 'OPERATING_UNIT';

       p_external_payee_tab_type(i).supplier_site_id := j.supplier_site_id;

       p_external_payee_tab_type(i).Payee_Party_Site_Id := j.party_site_id;

       p_ext_payee_id_tab_type(i).ext_payee_id := j.ext_payee_id;

       

iby_disbursement_setup_pub.update_external_payee(

p_api_version            => 1.0

,p_init_msg_list          => 'T'

,p_ext_payee_tab          => p_external_payee_tab_type

,p_ext_payee_id_tab       => p_ext_payee_id_tab_type

,x_return_status          => x_return_status

,x_msg_count              => x_msg_count

,x_msg_data               => x_msg_data

,x_ext_payee_status_tab   => l_payee_upd_status

);

DBMS_OUTPUT.PUT_LINE ('External Payee Update :' || j.ext_payee_id);

DBMS_OUTPUT.PUT_LINE ('x_return_status: ' || x_return_status);

IF x_return_status = 'E'

THEN

FOR k IN l_payee_upd_status.FIRST .. l_payee_upd_status.LAST

LOOP

DBMS_OUTPUT.put_line('Error Message from table type : '

                                  || l_payee_upd_status (k).Payee_update_Msg);

END LOOP;

END IF;

i := 0;

----------

---COMMIT;

----------

    END LOOP;

 EXCEPTION

    WHEN OTHERS

    THEN

       DBMS_OUTPUT.PUT_LINE ('Error ' || SQLERRM);

 END;

Query to get Invoice Payment Voucher Details in Oracle APPS

SELECT chk.doc_sequence_value payment_voucher

        ,chk.currency_code payment_currency

        ,chk.check_date payment_date

        ,check_number payment_number

        ,chk.future_pay_due_date check_date             

        ,chk.payment_method_code payment_type

        ,bank.bank_name bank_name

        ,chk.bank_account_num bank_account_num

        ,chk.vendor_name paid_to

        ,chk.amount

        ,aia.invoice_num invoice_number

        ,aia.invoice_date invoice_date

        ,aia.description invoice_description

        ,aia.invoice_amount invoice_amount

        ,supplier_bank.bank_name paid_to_bank_name

        ,supplier_bank.bank_account_num paid_to_bank_account_num

        ,chk.exchange_rate conversion_rate

        ,TO_CHAR ( (NVL (chk.exchange_rate, 1) * chk.amount), '999,999,999.99') functional_amount

    FROM ap_invoice_payments_all ipa

        ,ap_checks_all chk

        ,ap_invoices_all aia

        ,fnd_user u

        ,ce_bank_accounts bank_acct

        ,ce_banks_v bank

        ,xle_entity_profiles le

        ,(SELECT aps.vendor_id,

                 hop_bank.organization_name bank_name,

                 ieba.bank_account_num

            FROM hz_parties hzp,

                 ap_suppliers aps,

                 iby_external_payees_all hepa,

                 iby_pmt_instr_uses_all ipiua,

                 iby_ext_bank_accounts ieba,

                 hz_parties hzp_bank,

                 hz_organization_profiles hop_bank

           WHERE hzp.party_id = aps.party_id

             AND hzp.party_id = hepa.payee_party_id

             AND hepa.ext_payee_id = ipiua.ext_pmt_party_id(+)

             AND ipiua.instrument_id = ieba.ext_bank_account_id(+)

             AND ieba.bank_id = hzp_bank.party_id(+)

             AND hop_bank.party_id(+) = hzp_bank.party_id

             AND hepa.supplier_site_id IS NULL) supplier_bank

   WHERE le.legal_entity_id = chk.legal_entity_id

     AND chk.check_id = ipa.check_id

     AND bank.bank_party_id = bank_acct.bank_id

     AND bank_acct.bank_account_name = chk.bank_account_name

     AND bank_acct.bank_account_num = chk.bank_account_num

     AND aia.invoice_id = ipa.invoice_id

     AND aia.vendor_id = supplier_bank.vendor_id(+)

     AND chk.created_by = u.user_id(+)

     AND chk.last_updated_by = u.user_id(+)

     AND chk.status_lookup_code <> 'VOIDED'

     --and chk.check_id = 1234567890

     AND chk.check_number = NVL (:p_check_number, chk.check_number)

     AND NVL (chk.doc_sequence_value, 0) BETWEEN NVL (

:p_voucher_no_from,

NVL (

   chk.doc_sequence_value,

   0))

AND NVL (

:p_voucher_no_to,

NVL (

   chk.doc_sequence_value,

   0))

AND ipa.org_id = NVL (:p_org_id, ipa.org_id)

AND chk.check_date BETWEEN NVL (:p_from_date, chk.check_date)

                            AND NVL (:p_to_date, chk.check_date)

ORDER BY chk.doc_sequence_value,

         chk.check_number,

         aia.invoice_num,

         aia.invoice_date

Query to get Concurrent Programs that are Errored or Warning in Oracle APPS

  SELECT fcr.request_id

        ,fcp.user_concurrent_program_name

        ,TO_CHAR (TRUNC (actual_start_date), 'DD-MON-RRRR') start_date

        ,ROUND (

            ( (NVL (fcr.actual_completion_date, SYSDATE)

               - fcr.actual_start_date)

             * 60

             * 24),

            2) time_in_min

        ,phase.meaning phase

        ,status.meaning status

    FROM fnd_concurrent_requests fcr

        ,fnd_concurrent_programs_tl fcp

        ,fnd_lookup_values status

        ,fnd_lookup_values phase

   WHERE 1 = 1 

     AND fcr.concurrent_program_id = fcp.concurrent_program_id

     AND (fcr.status_code <> 'C' 

         OR fcr.phase_code <> 'C')

     AND fcr.status_code = status.lookup_code

     AND status.lookup_type = 'CP_STATUS_CODE'

     AND status.view_application_id = 0

     AND fcr.phase_code = phase.lookup_code

     AND phase.lookup_type = 'CP_PHASE_CODE'

     AND phase.view_application_id = 0

ORDER BY fcp.user_concurrent_program_name, fcr.request_id

Difference between Cursor and Ref Cursor in Oracle APPS

• A normal PL/SQL cursor is static in definition. Ref cursors may be dynamically opened based on some logic.
• A cursor cannot be returned to a client. A Ref cursor can be returned to a client.
• A cursor can be Global. A ref cursor cannot be Global.
• A ref cursor can be passed from subroutine to subroutine. A cursor cannot be passed from subroutine.
• Static SQL i.e. not using a Ref cursor is much more efficient then using ref cursors.

Sample API Script for Creating Employee Dependent Contact Details in Oracle HRMS(APPS)

This is used to migrate Employee Dependent (Contact) Details.

Table Script:

DROP TABLE XXSCHEMA.XX_EMP_CONTACT_STG;

DROP SYNONYM APPS.XX_EMP_CONTACT_STG;

CREATE TABLE XXSCHEMA.XX_EMP_CONTACT_STG

(SL_NO NUMBER

,BUSINESS_GROUP_ID NUMBER

,EMPLOYEE_NUMBER VARCHAR2(30)

,PERSON_ID NUMBER

,CONTACT_LAST_NAME VARCHAR2(150)

,CONTACT_MIDDLE_NAME VARCHAR2(60)

,CONTACT_FIRST_NAME VARCHAR2(150)

,CONTACT_NAME_TITLE VARCHAR2(30)

,CONTACT_GENDER VARCHAR2(30)

,CONTACT_GENDER_CODE VARCHAR2(1)

,CONTACT_DOB DATE

,CONTACT_NATIONAL_IDENTIFIER VARCHAR2(30)

,CONTACT_RELATIONSHIP_TYPE      varchar2(80)

,CONTACT_RELATIONSHIP_TYPE_CODE varchar2(30)

,CONTACT_EFFECTIVE_DATE DATE

,SPOUSE_EFFECTIVE_DATE DATE

,CONTACT_PRIMARY_CONTACT VARCHAR2(1)

,CONTACT_PERSONAL_RELATIONSHIP VARCHAR2(1)

,CONTACT_BENEFICIARY VARCHAR2(1)

,CONTACT_DEPENDENT VARCHAR2(1)

,CONTACT_RELATIONSHIP_ID      NUMBER

,CTR_OBJECT_VERSION_NUMBER    NUMBER

,PER_PERSON_ID                NUMBER

,PER_OBJECT_VERSION_NUMBER    NUMBER

,PER_EFFECTIVE_START_DATE      DATE

,PER_EFFECTIVE_END_DATE        DATE

,FULL_NAME                    VARCHAR2(240)

,PER_COMMENT_ID              NUMBER 

--,NAME_COMBINATION_WARNING      BOOLEAN

--,ORIG_HIRE_WARNING            BOOLEAN

,PROCESS_STATUS VARCHAR2(1)

,PROCESS_MESSAGE VARCHAR2(4000)

);

CREATE SYNONYM APPS.XX_EMP_CONTACT_STG FOR XXSCHEMA.XX_EMP_CONTACT_STG;

Sample Code using Anonymous Block:

DECLARE 

    l_business_group_id                  per_business_groups.business_group_id%TYPE;

    l_contact_rel_id                     per_contact_relationships.contact_relationship_id%TYPE; 

    l_ctr_object_ver_num                 per_contact_relationships.object_version_number%TYPE; 

    l_contact_person                     per_all_people_f.person_id%TYPE; 

    l_object_version_number              per_contact_relationships.object_version_number%TYPE; 

    l_per_effective_start_date           DATE; 

    l_per_effective_end_date             DATE; 

    l_full_name                          per_all_people_f.full_name%TYPE; 

    l_per_comment_id                     per_all_people_f.comment_id%TYPE; 

    l_name_comb_warning                  BOOLEAN; 

    l_orig_hire_warning                  BOOLEAN;

 

    l_contact_exp                        EXCEPTION;

    

    

    l_person_id                         per_all_people_f.person_id%TYPE;

    l_contact_type                      hr_lookups.lookup_code%TYPE;    

    l_contact_gender                    hr_lookups.lookup_code%TYPE;

    l_contact_titile_cnt                NUMBER;

    l_effective_date                    DATE;

    l_date                              date;

l_contact_effective_date            DATE;

    l_process_status                    VARCHAR2(1);

    l_process_message                   VARCHAR2(4000);

    

    l_user_id                           fnd_user.user_id%TYPE;    

    l_resp_id                           fnd_responsibility_tl.responsibility_id%TYPE;

    l_resp_appl_id                      fnd_responsibility_tl.application_id%TYPE;

    

    

    CURSOR validate_emp_contacts

    IS

        SELECT *

          FROM xx_emp_contact_stg

         WHERE NVL(process_status, 'N') IN ('N', 'E')

            AND employee_number NOT IN ('1234567890')

            --AND sl_no = 1

         ;

    

    CURSOR emp_contacts

    IS

        SELECT *

          FROM xx_emp_contact_stg

         WHERE process_status IN ('V')

          --AND sl_no = 1

          -- AND 1=2

         ;

BEGIN

    l_user_id := NULL;

    l_resp_id := NULL;

    l_resp_appl_id := NULL;

    l_business_group_id := NULL;

    

    

    BEGIN

        SELECT user_id

          INTO l_user_id

          FROM fnd_user

         WHERE user_name = 'XX_USER';

    EXCEPTION

    WHEN OTHERS THEN

        l_user_id := NULL;

    END;

    

    BEGIN

        SELECT responsibility_id, application_id 

          INTO l_resp_id, l_resp_appl_id

          FROM fnd_responsibility_tl

         WHERE responsibility_name = 'Global HRMS Manager'

           AND language = USERENV('LANG')

           ;

    EXCEPTION

    WHEN OTHERS THEN

        l_resp_id := NULL;

        l_resp_appl_id := NULL;

    END;

    

    -- Initialize apps session

    fnd_global.apps_initialize(user_id      => l_user_id

                              ,resp_id      => l_resp_id

                              ,resp_appl_id => l_resp_appl_id

                              );

    

    BEGIN

        SELECT fnd_profile.value('PER_BUSINESS_GROUP_ID')

          INTO l_business_group_id

          FROM dual;

    EXCEPTION

    WHEN OTHERS THEN

        l_business_group_id := NULL;

    END;

    

    IF l_business_group_id IS NOT NULL THEN

        FOR rec_validate_emp_contacts IN validate_emp_contacts

        LOOP

            l_person_id   := NULL;

            l_contact_type := NULL;

            l_contact_gender := NULL;

            l_contact_titile_cnt := 0;

            l_effective_date := NULL;

l_contact_effective_date := NULL;

            

            IF rec_validate_emp_contacts.employee_number IS NOT NULL THEN

                BEGIN

                    SELECT person_id, original_date_of_hire--, start_date

                      INTO l_person_id, l_effective_date

                      FROM per_all_people_f

                     WHERE TRIM(employee_number) = TRIM(rec_validate_emp_contacts.employee_number)

                       AND business_group_id = l_business_group_id

                       AND SYSDATE BETWEEN effective_start_date AND effective_end_date

                       ;

                EXCEPTION

                WHEN NO_DATA_FOUND THEN

                    l_person_id := NULL;

                    l_process_status := 'E';

                    l_process_message := 'Employee Number '||rec_validate_emp_contacts.employee_number||' does not exists or end dated';

                WHEN OTHERS THEN

                    l_person_id := NULL;

                    l_process_status := 'E';

                    l_process_message := 'Error while validating Employee Number '||rec_validate_emp_contacts.employee_number;

                END;

            ELSE

                l_process_status := 'E';

                l_process_message := 'Employee Number Cannot be Null.';

            END IF;

            

            IF rec_validate_emp_contacts.contact_relationship_type IS NOT NULL THEN

                BEGIN

                    SELECT hl.lookup_code

                      INTO l_contact_type

                      FROM hr_lookups hl

                     WHERE UPPER(TRIM(hl.meaning))=UPPER(TRIM(rec_validate_emp_contacts.contact_relationship_type))

                       AND hl.lookup_type = 'CONTACT';

                EXCEPTION

                WHEN NO_DATA_FOUND THEN

                    l_process_status := 'E';

                    l_process_message := l_process_message||' Contact Relationship Type '||rec_validate_emp_contacts.contact_relationship_type||' does not exists in Lookup Type CONTACT.';

                WHEN OTHERS THEN

                    l_process_status := 'E';

                    l_process_message := l_process_message||' Invalid Contact Relationship Type '||rec_validate_emp_contacts.contact_relationship_type||'.';

                END;

            ELSE

                l_process_status := 'E';

                l_process_message := 'Contact Relationship Type Cannot be Null.';

            END IF;

            

            IF rec_validate_emp_contacts.contact_gender IS NOT NULL THEN            

                BEGIN

                    SELECT hl.lookup_code

                      INTO l_contact_gender

                      FROM hr_lookups hl

                     WHERE UPPER(TRIM(hl.meaning))=UPPER(TRIM(rec_validate_emp_contacts.contact_gender))

                       AND hl.lookup_type = 'SEX';

                EXCEPTION

                WHEN NO_DATA_FOUND THEN

                    l_process_status := 'E';

                    l_process_message := l_process_message||' Contact Gender '||rec_validate_emp_contacts.contact_gender||' does not exists in Lookup Type SEX.';

                WHEN OTHERS THEN

                    l_process_status := 'E';

                    l_process_message := l_process_message||' Invalid Contact Gender '||rec_validate_emp_contacts.contact_gender||'.';

                END;

            ELSE

                l_process_status := 'E';

                l_process_message := 'Contact Gender Cannot be Null.';

            END IF;

            IF rec_validate_emp_contacts.contact_name_title IS NOT NULL THEN

                SELECT COUNT(1)

                  INTO l_contact_titile_cnt

                  FROM hr_lookups hl

                 WHERE UPPER(TRIM(hl.meaning))=UPPER(TRIM(rec_validate_emp_contacts.contact_name_title))

                   AND hl.lookup_type = 'TITLE';

                   

                IF l_contact_titile_cnt = 0 THEN

                    l_process_status := 'E';

                    l_process_message := l_process_message||' Contact Name Title '||rec_validate_emp_contacts.contact_name_title||' does not exists in Lookup Type TITLE.';

                END IF;

            ELSE

                l_process_status := 'E';

                l_process_message := l_process_message||' Contact Name Title Cannot be Null.';

            END IF;

IF UPPER(TRIM(rec_validate_emp_contacts.contact_relationship_type)) <> 'SPOUSE' THEN

IF l_effective_date > rec_validate_emp_contacts.contact_dob THEN

l_contact_effective_date := l_effective_date;

ELSE

l_contact_effective_date := NULL;

END IF;

            END IF;

            UPDATE xx_emp_contact_stg

               SET business_group_id = l_business_group_id

                  ,person_id = l_person_id

                  ,contact_gender_code = l_contact_gender

                  ,contact_relationship_type_code = l_contact_type

                  ,spouse_effective_date = DECODE(UPPER(TRIM(rec_validate_emp_contacts.contact_relationship_type)), 'SPOUSE', l_effective_date, NULL)

  ,contact_effective_date = l_contact_effective_date

                  ,process_status = 'V'

                  ,process_message = NULL

             WHERE sl_no = rec_validate_emp_contacts.sl_no;

        END LOOP;

    ELSE

        UPDATE xx_emp_contact_stg

           SET business_group_id = NULL

              ,process_status = 'E'

              ,process_message = 'Error while fetching business_group_id '

         WHERE process_status IN ('N','E');

    END IF;    

    

    FOR load_emp_contacts IN emp_contacts

    LOOP

            l_contact_rel_id   := null;

            l_ctr_object_ver_num := null;

            l_date := NULL;

            

             dbms_output.put_line('Employee Number: '||load_emp_contacts.employee_number);   

             dbms_output.put_line('Person ID: '||load_emp_contacts.person_id);

            

            IF UPPER(TRIM(load_emp_contacts.contact_relationship_type)) = 'SPOUSE' THEN

                SELECT load_emp_contacts.spouse_effective_date

                   INTO l_date

                   FROM dual;

            ELSE

                SELECT NVL(load_emp_contacts.contact_effective_date, load_emp_contacts.contact_dob)

                  INTO l_date

                  FROM dual;

            END IF;

            

            -- Create Employee Contact

            -- -------------------------------------

            hr_contact_rel_api.create_contact

            (    -- Input data elements

               -- -----------------------------

                 p_validate                          => FALSE

                ,p_start_date                        => /*DECODE(load_emp_contacts.contact_relationship_type, 'SPOUSE', load_emp_contacts.spouse_effective_date

                                                                                                                    , load_emp_contacts.contact_dob)*/

                                                        l_date

                ,p_business_group_id                 => load_emp_contacts.business_group_id

                ,p_person_id                         => load_emp_contacts.person_id

                ,p_contact_type                      => load_emp_contacts.contact_relationship_type_code

                ,p_date_start                        => l_date

                ,p_title                             => UPPER(TRIM(load_emp_contacts.contact_name_title))-- 'MR.'

                ,p_last_name                         => load_emp_contacts.contact_last_name  

                ,p_middle_names                      => load_emp_contacts.contact_middle_name 

                ,p_first_name                        => load_emp_contacts.contact_first_name 

                ,p_date_of_birth                     => load_emp_contacts.contact_dob

                ,p_sex                               => load_emp_contacts.contact_gender_code --'M'

                ,p_primary_contact_flag              => load_emp_contacts.contact_primary_contact

                ,p_personal_flag                     => load_emp_contacts.contact_personal_relationship --'Y'

                ,p_beneficiary_flag                  => load_emp_contacts.contact_beneficiary

                ,p_dependent_flag                    => load_emp_contacts.contact_dependent

                --,p_effective_date                    => l_date

                -- Output data elements

                -- --------------------------------

                ,p_contact_relationship_id           => l_contact_rel_id

                ,p_ctr_object_version_number         => l_ctr_object_ver_num

                ,p_per_person_id                     => l_contact_person

                ,p_per_object_version_number         => l_object_version_number

                ,p_per_effective_start_date          => l_per_effective_start_date

                ,p_per_effective_end_date            => l_per_effective_end_date

                ,p_full_name                         => l_full_name

                ,p_per_comment_id                    => l_per_comment_id

                ,p_name_combination_warning          => l_name_comb_warning

                ,p_orig_hire_warning                 => l_orig_hire_warning

            );

         

        IF l_contact_rel_id is null then

            dbms_output.put_line('Create Contact Relationship failed '||sqlerrm);

            UPDATE xx_emp_contact_stg

               SET process_status = 'E'

                  ,process_message = 'Create Contact Relationship failed '

             WHERE sl_no = load_emp_contacts.sl_no;

            --raise l_contact_exp;

        ELSE

            dbms_output.put_line('Contact Relationship Created');     

            UPDATE xx_emp_contact_stg

               SET contact_relationship_id = l_contact_rel_id

                  ,ctr_object_version_number = l_ctr_object_ver_num

                  ,per_person_id = l_contact_person

                  ,per_object_version_number = l_object_version_number

                  ,per_effective_start_date = l_per_effective_start_date

                  ,per_effective_end_date = l_per_effective_end_date

                  ,full_name = l_full_name

                  ,per_comment_id = l_per_comment_id

                  --,name_combination_warning = l_name_comb_warning

                  --,orig_hire_warning = l_orig_hire_warning

                  ,process_status = 'S'

                  ,process_message = 'Success'

             WHERE sl_no = load_emp_contacts.sl_no;

        END IF;

     

    END LOOP;

     --COMMIT;

    

EXCEPTION

WHEN OTHERS THEN

    --ROLLBACK;

    dbms_output.put_line('Inside Exception: '||SQLERRM);

END;

/

Query to validated migrated Employee Dependent Details

ORA-20001: FLEX-NULL REQUIRED SEGMENT: N, SEGMENT_NAME, 'XX_SEGMENT_NAME' while creating Employee Contact hr_contact_rel_api.create_contact

While creating relationship type contact for employee using hr_contact_rel_api.create_contact API, getting "ORA-20001: FLEX-NULL REQUIRED SEGMENT: N, SEGMENT_NAME, 'XX_SEGMENT_NAME'" error. When you face such error please validate "Further Contact R'ship Info" flex field has any mandatory values as shown in below screenshot.

Please pass value for flex value and test it.