Query to get Sales Invoice(Receivables) Details in R12

SELECT RA_CUST_TRX_TYPES_ALL.TYPE,

       RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATE_DATE,

       RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATED_BY,

       RA_CUSTOMER_TRX_LINES_ALL.CREATION_DATE,

       RA_CUSTOMER_TRX_LINES_ALL.CREATED_BY,

       RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID,

       RA_CUSTOMER_TRX_LINES_ALL.LINE_NUMBER,

       RA_CUSTOMER_TRX_LINES_ALL.SET_OF_BOOKS_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INVENTORY_ITEM_ID,

       RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_INVOICED,

       RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_CREDITED,

       RA_CUSTOMER_TRX_LINES_ALL.UNIT_STANDARD_PRICE,

       RA_CUSTOMER_TRX_LINES_ALL.UNIT_SELLING_PRICE,

       RA_CUSTOMER_TRX_LINES_ALL.SALES_ORDER,

       RA_CUSTOMER_TRX_LINES_ALL.SALES_ORDER_LINE,

       RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE,

       RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT,

       RA_CUSTOMER_TRX_LINES_ALL.TAX_RATE,

       RA_CUSTOMER_TRX_LINES_ALL.UOM_CODE,

       RA_CUSTOMER_TRX_LINES_ALL.ORG_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE7,

       RA_CUSTOMER_TRX_LINES_ALL.LINK_TO_CUST_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL.WAREHOUSE_ID,

       RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID,

       RA_CUSTOMER_TRX_ALL.TRX_NUMBER,

       RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID,

       RA_CUSTOMER_TRX_ALL.TRX_DATE,

       RA_CUSTOMER_TRX_ALL.SOLD_TO_CUSTOMER_ID,

       OE_ORDER_HEADERS_ALL.SOLD_TO_CONTACT_ID,

       RA_CUSTOMER_TRX_ALL.SOLD_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.BILL_TO_CUSTOMER_ID,

       RA_CUSTOMER_TRX_ALL.BILL_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.SHIP_TO_CUSTOMER_ID,

       RA_CUSTOMER_TRX_ALL.SHIP_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.TERM_ID,

       RA_CUSTOMER_TRX_ALL.TERM_DUE_DATE,

       RA_CUSTOMER_TRX_ALL.PRIMARY_SALESREP_ID,

       RA_CUSTOMER_TRX_ALL.PRINTING_ORIGINAL_DATE,

       RA_CUSTOMER_TRX_ALL.PURCHASE_ORDER,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_DATE,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_RATE,

       RA_CUSTOMER_TRX_ALL.TERRITORY_ID,

       RA_CUSTOMER_TRX_ALL.INVOICE_CURRENCY_CODE,

       RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG,

       RA_CUSTOMER_TRX_ALL.PAYING_SITE_USE_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.CUSTOMER_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.INVENTORY_ITEM_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.SALES_ORDER,

       RA_CUSTOMER_TRX_LINES_ALL1.SALES_ORDER_LINE,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_CONTEXT,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_ATTRIBUTE6,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_ATTRIBUTE7,

       RA_CUSTOMER_TRX_LINES_ALL1.WAREHOUSE_ID,

       RA_CUSTOMER_TRX_ALL.STATUS_TRX,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_RATE_TYPE,

       RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE,

       RA_CUST_TRX_TYPES_ALL.LAST_UPDATE_DATE,

       OE_ORDER_HEADERS_ALL.LAST_UPDATE_DATE,

       OE_ORDER_HEADERS_ALL.SALES_CHANNEL_CODE,

       OE_ORDER_HEADERS_ALL.ORDER_TYPE_ID,

       OE_ORDER_HEADERS_ALL.PAYMENT_TYPE_CODE,

       OE_ORDER_HEADERS_ALL.FREIGHT_TERMS_CODE,

       OE_ORDER_LINES_ALL.LINE_CATEGORY_CODE,

       OE_ORDER_LINES_ALL.ITEM_TYPE_CODE,

       OE_ORDER_LINES_ALL.SHIPMENT_NUMBER,

       OE_ORDER_LINES_ALL.SHIP_FROM_ORG_ID,

       OE_ORDER_LINES_ALL.PROJECT_ID,

       OE_ORDER_LINES_ALL.TASK_ID,

       OE_ORDER_LINES_ALL.OPTION_NUMBER,

       OE_ORDER_LINES_ALL.COMPONENT_NUMBER,

       OE_ORDER_LINES_ALL.SOURCE_TYPE_CODE,

       OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID,

       RA_CUSTOMER_TRX_ALL.LEGAL_ENTITY_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE11

  FROM RA_CUSTOMER_TRX_LINES_ALL  RA_CUSTOMER_TRX_LINES_ALL1,

       RA_CUSTOMER_TRX_LINES_ALL,

       RA_CUSTOMER_TRX_ALL,

       RA_CUST_TRX_TYPES_ALL,

       OE_ORDER_HEADERS_ALL,

       OE_ORDER_LINES_ALL

 WHERE     RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID =

           RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID

       AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = 'Y'

       AND RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID =

           RA_CUST_TRX_TYPES_ALL.CUST_TRX_TYPE_ID

       AND RA_CUSTOMER_TRX_ALL.ORG_ID = RA_CUST_TRX_TYPES_ALL.ORG_ID

       AND RA_CUSTOMER_TRX_LINES_ALL.LINK_TO_CUST_TRX_LINE_ID =

           RA_CUSTOMER_TRX_LINES_ALL1.CUSTOMER_TRX_LINE_ID(+)

       AND RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT IN

               ('ORDER ENTRY', 'INTERCOMPANY')

       AND TO_NUMBER (RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6) =

           OE_ORDER_LINES_ALL.LINE_ID(+)

       AND OE_ORDER_LINES_ALL.HEADER_ID = OE_ORDER_HEADERS_ALL.HEADER_ID(+)

       AND RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE >=

           TO_DATE (:P_LAST_EXTRACT_DATE, 'MM/D D/YYYY HH24:MI:SS')

Query to get AP Invoice details in R12

SELECT AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER

      ,AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATED_BY

      ,AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.DIST_CODE_COMBINATION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.CREATED_BY

  ,AP_INVOICE_DISTRIBUTIONS_ALL.CREATION_DATE

  ,AP_INVOICES_ALL.INVOICE_ID

  ,AP_INVOICES_ALL.INVOICE_NUM

  ,AP_INVOICES_ALL.INVOICE_CURRENCY_CODE

  ,AP_INVOICES_ALL.VENDOR_ID

  ,AP_INVOICES_ALL.VENDOR_SITE_ID

  ,AP_INVOICES_ALL.INVOICE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE

  ,AP_INVOICES_ALL.INVOICE_RECEIVED_DATE

  ,AP_INVOICES_ALL.CREATION_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID

  ,PO_HEADERS_ALL.SEGMENT1

  ,PO_LINES_ALL.LINE_NUM

  ,PO_HEADERS_ALL.CREATION_DATE

  ,AP_INVOICES_ALL.LAST_UPDATE_DATE

  ,PO_HEADERS_ALL.LAST_UPDATE_DATE

  ,PO_LINES_ALL.LAST_UPDATE_DATE

  ,PO_LINES_ALL.ITEM_DESCRIPTION

  ,AP_INVOICE_DISTRIBUTIONS_ALL.DESCRIPTION

  ,AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE

  ,AP_INVOICES_ALL.WFAPPROVAL_STATUS

  ,AP_INVOICES_ALL.PAYMENT_STATUS_FLAG

  ,AP_INVOICES_ALL.CANCELLED_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE

  ,PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG

  ,AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID

  ,AP_INVOICES_ALL.TERMS_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_DATE

  ,AP_INVOICES_ALL.EXCHANGE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_TYPE

  ,AP_INVOICES_ALL.EXCHANGE_RATE_TYPE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE

  ,AP_INVOICES_ALL.EXCHANGE_RATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ACCTS_PAY_CODE_COMBINATION_ID

  ,AP_INVOICES_ALL.ACCTS_PAY_CODE_COMBINATION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID

  ,AP_INVOICES_ALL.SET_OF_BOOKS_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_INVOICED

  ,AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT

  ,AP_INVOICE_DISTRIBUTIONS_ALL.BASE_AMOUNT

  ,CASE WHEN AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE IS NULL 

        THEN PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE 

ELSE AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE 

   END

  ,AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_PRICE_VARIANCE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_VARIANCE

  ,PO_VENDORS.RECEIPT_REQUIRED_FLAG

  ,PO_VENDORS.INSPECTION_REQUIRED_FLAG

  ,PO_VENDOR_SITES_ALL.PURCHASING_SITE_FLAG

  ,PO_VENDORS.LAST_UPDATE_DATE

  ,AP_INVOICES_ALL.SOURCE

  ,AP_INVOICES_ALL.PROJECT_ID

  ,AP_INVOICES_ALL.TASK_ID

  ,AP_INVOICES_ALL.EXPENDITURE_TYPE

  ,AP_INVOICES_ALL.EXPENDITURE_ORGANIZATION_ID

  ,AP_INVOICES_ALL.PAYMENT_CURRENCY_CODE

  ,AP_INVOICES_ALL.PAYMENT_CROSS_RATE

  ,AP_INVOICES_ALL.REQUESTER_ID

  ,AP_INVOICE_LINES_ALL.LINE_NUMBER

  ,AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE

  ,AP_INVOICE_LINES_ALL.REQUESTER_ID

  ,AP_INVOICE_LINES_ALL.DESCRIPTION

  ,AP_INVOICES_ALL.LEGAL_ENTITY_ID

  FROM AP_INVOICE_DISTRIBUTIONS_ALL

      ,AP_INVOICE_LINES_ALL

  ,AP_INVOICES_ALL

  ,PO_LINES_ALL

  ,PO_HEADERS_ALL

  ,PO_VENDORS

  ,PO_VENDOR_SITES_ALL

  ,PO_DISTRIBUTIONS_ALL

  ,(SELECT AP_INVOICES_ALL.INVOICE_ID 

      FROM AP_INVOICES_ALL 

WHERE AP_INVOICES_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM/DD/Y YYY HH24:MI:SS') 

UNION 

SELECT AP_INVOICE_LINES_ALL.INVOICE_ID 

  FROM AP_INVOICE_LINES_ALL 

WHERE AP_INVOICE_LINES_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM /DD/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_HOLDS_ALL.INVOICE_ID 

  FROM AP_HOLDS_ALL 

WHERE AP_HOLDS_ALL.RELEASE_LOOKUP_CODE IS NULL 

   AND AP_HOLDS_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM/DD/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE > TO_DATE (:P_LAST_EXTRACT_DATE,'MM/D D/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL

      ,AP_INVOICE_DISTRIBUTIONS_ALL

  ,PO_HEADERS_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID

UNION 

    SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, PO_LINES_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID 

   AND PO_LINES_ALL.LAST_UPDATE_DATE > TO_DATE (:P_LAST_EXTRACT_DATE,'MM/DD/YYYY H H24:MI:SS') 

   ) TEMP 

 WHERE AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICES_ALL.INVOICE_ID 

   AND AP_INVOICES_ALL.VENDOR_ID = PO_VENDORS.VENDOR_ID(+) 

   AND AP_INVOICES_ALL.VENDOR_SITE_ID = PO_VENDOR_SITES_ALL.VENDOR_SITE_ID(+) 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_LINES_ALL.PO_HEADER_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID(+) 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = TEMP.INVOICE_ID 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICE_LINES_ALL.INVOICE_ID 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_LINE_NUMBER = AP_INVOICE_LINES_ALL.LINE_NUMBER 

Query to get Oracle Workflow attribute values

SELECT name attribute_name

      ,NVL(text_value, NVL(TO_CHAR(number_value), TO_CHAR(date_value))) VALUE

  FROM wf_item_attribute_values

 WHERE item_type = 'XXITEMTP'

   AND item_key = 'XXITEMTP001'

Query to get Errored Activities in Oracle Workflow

SELECT wav.display_name            activity

      ,wias.activity_result_code activity_result

  ,wias.error_name            error_name

  ,wias.error_message         error_message

  ,wias.error_stack           error_stack

   FROM wf_item_activity_statuses wias

       ,wf_process_activities      wpa

   ,wf_activities_vl           wav

   ,wf_activities_vl           wav1

   ,wf_items wi

  WHERE wias.item_type = wi.item_type

    AND wias.item_key = wi.item_key

    AND wias.activity_status = 'ERROR'

    AND wias.process_activity = wpa.instance_id

    AND wpa.activity_name = wav.name

    AND wpa.activity_item_type = wav.item_type

    AND wpa.process_name = wav1.name

    AND wpa.process_item_type = wav1.item_type

    AND wpa.process_version = wav1.version

    AND wi.parent_item_type = 'XXITEMTP'

    AND wi.parent_item_key = 'XXITEMTP001'

    AND wi.begin_date >= wav.begin_date

    AND wi.begin_date < NVL(wav.end_date, wi.begin_date + 1)

  ORDER BY wias.execution_time

Query to get Oracle Workflow Deferred Activities

SELECT COUNT(1)

      ,wias.item_type

  FROM apps.wf_items wi

      ,apps.wf_item_activity_statuses wias

  ,apps.wf_process_activities wpa

 WHERE wi.item_type = wias.item_type

   AND wi.item_key = wias.item_key

   AND wi.end_date IS NULL

   AND wias.end_date IS NULL

   AND wias.activity_status = 'DEFERRED'

   --AND wias.item_type = 'REQAPPRV'

   AND wias.item_type = wi.item_type

   AND wpa.instance_id(+) = wias.process_activity

GROUP BY wias.item_type

Query to check the Workflow Agent Listeners and their Statuses

SELECT fsc.component_name

      ,dq.owner

      ,dq.queue_table

      ,fsc.correlation_id

  FROM applsys.fnd_svc_components fsc

      ,applsys.wf_agents          wa

  ,dba_queues dq

 WHERE fsc.inbound_agent_name || fsc.outbound_agent_name = wa.name

   AND dq.owner || '.' || dq.name = wa.queue_name

   AND fsc.component_type LIKE 'WF_%AGENT%'

Query to get List of all Errored Oracle Workflow Activities for a given Item Type/Item Key

SELECT wac.display_name          activity

      ,wias.activity_result_code activity_result

  ,wias.error_name           error_name

  ,wias.error_message        error_message

  ,wias.error_stack          error_stack

  FROM wf_item_activity_statuses wias

      ,wf_process_activities wpa

      ,wf_activities_vl wac

      ,wf_activities_vl wap

      ,wf_items wi

 WHERE wias.item_type = 'XXITEMTP'

   AND wias.item_key = 'XXITEMTP001'

   AND wias.activity_status = 'ERROR'

   AND wias.process_activity = wpa.instance_id

   AND wpa.activity_name = wac.name

   AND wpa.activity_item_type = wac.item_type

   AND wpa.process_name = wap.name

   AND wpa.process_item_type = wap.item_type

   AND wpa.process_version = wap.version

   AND wi.item_type = 'XXITEMTP'

   AND wi.item_key = wias.item_key

   AND wi.begin_date >= wac.begin_date

   AND wi.begin_date < nvl(wac.end_date, wi.begin_date + 1)

 ORDER BY wias.execution_time

SQL Query to get Activity Statuses for all Workflow Activities of a given Item Type and Item Key in Oracle APPS

SELECT *

  FROM(

SELECT execution_time

      ,TO_CHAR(wias.begin_date, 'DD-MON-RR HH24:MI:SS') begin_date

  ,wap.display_name || '/' || wac.display_name activity

  ,wias.activity_status status

  ,wias.activity_result_code activity_result

  ,wias.assigned_user ass_user

  FROM wf_item_activity_statuses wias

      ,wf_process_activities wpa

      ,wf_activities_vl wac

      ,wf_activities_vl wap

      ,wf_items         wi

 WHERE wias.item_type = 'XXITEMTP'

   AND wias.item_key = 'XXITEMTP001'

   AND wias.process_activity = wpa.instance_id

   AND wpa.activity_name = wac.name

   AND wpa.activity_item_type = wac.item_type

   AND wpa.process_name = wap.name

   AND wpa.process_item_type = wap.item_type

   AND wpa.process_version = wap.version

   AND wi.item_type = 'XXITEMTP'

   AND wi.item_key = wias.item_key

   AND wi.begin_date >= wac.begin_date

   AND wi.begin_date < nvl(wac.end_date, wi.begin_date + 1)

UNION ALL

SELECT execution_time

      ,TO_CHAR(wias.begin_date, 'DD-MON-RR HH24:MI:SS') begin_date

  ,wap.display_name || '/' || wac.display_name activity

  ,wias.activity_status status

  ,wias.activity_result_code activity_result

  ,wias.assigned_user ass_user

  FROM wf_item_activity_statuses_h wias

      ,wf_process_activities wpa

      ,wf_activities_vl wac

      ,wf_activities_vl wap

      ,wf_items wi

  WHERE wias.item_type = 'XXITEMTP'

    AND wias.item_key = 'XXITEMTP001'

    AND wias.process_activity = wpa.instance_id

    AND wpa.activity_name = wac.name

    AND wpa.activity_item_type = wac.item_type

    AND wpa.process_name = wap.name

    AND wpa.process_item_type = wap.item_type

    AND wpa.process_version = wap.version

    AND wi.item_type = 'XXITEMTP'

    AND wi.item_key = wias.item_key

    AND wi.begin_date >= wac.begin_date

    AND wi.begin_date < nvl(wac.end_date, wi.begin_date + 1)

)

  ORDER BY begin_date

          ,execution_time

Query to get Notifications sent by a given Oracle Workflow

SELECT wn.notification_id nid

      ,wn.context

      ,wn.group_id

      ,wn.status

      ,wn.mail_status

      ,wn.message_type

      ,wn.message_name

      ,wn.access_key

      ,wn.priority

      ,wn.begin_date

      ,wn.end_date

      ,wn.due_date

      ,wn.callback

      ,wn.recipient_role

      ,wn.responder

      ,wn.original_recipient

      ,wn.from_user

      ,wn.to_user

      ,wn.subject

  FROM wf_notifications wn

      ,wf_item_activity_statuses wias

 WHERE wn.group_id = wias.notification_id

   AND wias.item_type = 'XXITEMTP'

   AND wias.item_key = 'XXITEMTP001'

Query to Select all Oracle Workflow Items for a given Item Type

SELECT item_type

      ,item_key

  ,TO_CHAR(begin_date, 'DD-MON-RR HH24:MI:SS') begin_date

  ,TO_CHAR(end_date, 'DD-MON-RR HH24:MI:SS') end_date

  ,root_activity activity

  FROM apps.wf_items

 WHERE item_type = 'XXITEMTP'

   AND end_date IS NULL

 ORDER BY to_date(begin_date, 'DD-MON-YYYY hh24:mi:ss')

 ASC

How to add LOV to the Manual Adjustment reasons in Comp off Plan Type ?(Doc ID 2483891.1)

 Use ORA_ANC_COMP_ADJRSN from Manage Common Lookup 

Query to get Employee Absence details in Oracle APPS

SELECT /*+ rule*/

       paaf.person_id

  ,paaf.assignment_id

  ,flv.meaning absence_category

  ,c.name absence_type

  ,flv1.meaning absence_reason

  ,abs.date_notification

  ,abs.date_projected_start

  ,abs.date_projected_end

  ,abs.date_start

  ,abs.date_end

  ,abs.absence_days

  ,abs.attribute1

  ,abs.attribute2

  ,abs.attribute3

  ,abs.attribute4

  ,abs.attribute5

  ,abs.attribute6

  ,abs.attribute7

  ,abs.attribute8

  ,abs.attribute9

  ,abs.attribute10

  ,abs.attribute11

  ,abs.attribute12

  ,abs.attribute13

  ,abs.attribute14

  ,abs.attribute15

  ,abs.attribute16

  ,abs.attribute17

  ,abs.attribute18

  ,abs.attribute19

  ,abs.attribute20

  ,b.admission_code

  ,b.admission_date

  ,b.amendment_date

      ,b.amendment_reason

  ,b.concatenated_segments

  ,b.contact_grade

      ,b.contact_type

  ,b.discharge_date

  ,b.disease_name

      ,b.hospital_name

  ,b.leave_amended

  ,b.leave_salary_paid

      ,b.physician_approved_accident

  ,b.physician_name

      ,b.resumption_date

  ,b.context

  FROM per_absence_attendances abs

      ,per_absence_attendance_types c

      ,per_abs_attendance_reasons d

      ,per_absence_attendances_dfv b

      ,fnd_lookup_values flv1

      ,fnd_lookup_values flv

      ,per_all_assignments_f paaf          

 WHERE abs.person_id = paaf.person_id

   AND abs.ROWID = b.row_id

   AND abs.absence_attendance_type_id = c.absence_attendance_type_id(+)

   AND abs.abs_attendance_reason_id = d.abs_attendance_reason_id(+)

   AND d.name = flv1.lookup_code(+)

   AND flv1.lookup_type(+) = 'ABSENCE_REASON'

   AND flv.lookup_code(+) = c.absence_category

   AND flv.lookup_type(+) = 'ABSENCE_CATEGORY'

ORDER BY abs.person_id

        ,absence_type

,date_start 

DESC

Oracle HRMS, Absence Management & PMS Interview Questions

• What are the tables impacted while creating a new Employee?
• What are the mandatory fields require to create an employee In Employee Screen?
• What are the base tables of HRMS?
• What is the impact on base tables while Rehiring an Employee?
• What is the impact on base tables while Terminating an Employee?
• What is the process to create Business Group?
• What are the mandatory KFFs & DFFs for Creating Business Group?
• What are EIT & SITs and difference between them ?
• What is bursting file do in XML publisher Report?
• What are the ways to create an XML Publisher Report?
• Absence Management Setup Steps and what are the Fast Formulas used in Absence Management?
• What are the main Absence Management Tables?
• What are the Performance Management setup steps?
• What are the Appraisal Document statuses?
• What are the APIs used in HRMS and what are mandatory parameters for API?
• What is object version number, p_validate and what is the use?
• Payroll basic tables and what is costing and payroll setup.
• What is Payroll run process and what is the use of element link.
• How to attach Element to all employees?

Query to get Internal Bank Details in R12

 SELECT items.item_number

      ,itemcats.category_id

  ,itemcats.sequence_number

  ,itemcats.alt_item_cat_code

  ,ect.description

  ,ect.category_name

  FROM egp_item_cat_assignments itemcats

      ,egp_category_sets_b catsets

  ,egp_system_items_b items

  ,inv_org_parameters org

  ,egp_categories_tl ect

 WHERE catsets.category_set_id = itemcats.category_set_id

   AND items.inventory_item_id = itemcats.inventory_item_id

   AND org.organization_id = items.organization_id

   AND itemcats.category_id=ect.category_id

   AND ( ( catsets.control_level = 1

AND itemcats.organization_id = org.master_organization_id 

     )

  OR ( catsets.control_level = 2

AND itemcats.organization_id = org.organization_id

    ) 

)

Query to get Vendor Bank Detail in Oracle Fusion

SELECT DISTINCT psv.vendor_id

      ,psv.vendor_name

  ,pssa.vendor_site_id

      ,accts.ext_bank_account_id

      ,bank.party_name bank_name

      ,branch.bank_branch_name

      ,accts.bank_account_type

      ,accts.country_code

      ,accts.bank_account_name

      ,accts.attribute1 ifsc_code

      ,accts.bank_account_num

      ,accts.currency_code

  FROM poz_suppliers_v psv

      ,poz_supplier_sites_all_m pssa

      ,iby_pmt_instr_uses_all uses

      ,iby_external_payees_all payee

      ,iby_ext_bank_accounts accts

      ,hz_parties bank

      ,ce_bank_branches_v branch

 WHERE psv.vendor_id      = pssa.vendor_id

   AND psv.party_id       = payee.payee_party_id

   AND payee.ext_payee_id = uses.ext_pmt_party_id

   AND uses.instrument_id = accts.ext_bank_account_id

   AND accts.bank_id      = bank.party_id

   AND accts.branch_id    = branch.branch_party_id

   --AND payee.payment_function = 'PAYABLES_DISB'

Query to get Asset Details in Oracle Fusion

SELECT asset_number

      ,fab.asset_id

      ,fat.description

      ,fab.asset_type

      ,fcb.segment1||'-'||fcb.segment2 category

      ,fab.tag_number

      ,fb.book_type_code

      ,fabc.book_class

      ,fb.cost

      ,fb.recoverable_cost

      ,(SELECT SUM(deprn_reserve)

  FROM fa_deprn_detail

         WHERE asset_id = fab.asset_id

   AND deprn_run_date = (SELECT MAX(deprn_run_date)

   FROM fa_deprn_detail

  WHERE asset_id = fab.asset_id

) depriciation_cost

  FROM fa_additions_b fab

      ,fa_additions_tl fat

      ,fa_categories_b fcb

      ,fa_books fb

      ,fa_book_controls fabc

 WHERE 1=1

   AND fab.asset_id = fat.asset_id 

   AND fat.language = USERENV('LANG')

   AND fcb.category_id=fab.asset_category_id

   AND fb.asset_id=fab.asset_id

   AND fb.depreciate_flag = 'NO'

   AND fab.asset_number = 123456789

SQL Query to get Item Details in Oracle Fusion

SELECT esi.inventory_item_id

      ,esi.item_number

      ,esi.organization_id

      ,iop.organization_code  

      ,esi.description

      ,uomt.unit_of_measure

      ,esi.item_type

      ,esi.enabled_flag

      ,esi.planner_code planner

      ,esi.safety_stock_planning_method

  FROM inv_org_parameters iop

      ,egp_system_items  esi

      ,inv_units_of_measure_tl uomt

      ,inv_units_of_measure_b uomb

 WHERE 1=1

   AND iop.organization_id = esi.organization_id

   AND uomb.uom_code = esi.primary_uom_code

   AND uomt.unit_of_measure_id (+)= uomb.unit_of_measure_id

   AND uomt.language = 'US'

   AND esi.item_number = 'ITEM_NUM_001'

SQL Query to get the Contexts for Formula Type in Oracle Fusion

SELECT ft.base_formula_type_name

      ,base_context_name 

  FROM ff_contexts_vl con

      ,ff_ftype_context_usages fcu

  ,ff_formula_types_vl ft

 WHERE ft.formula_type_id = ft.formula_type_id

   AND fcu.formula_type_id = ft.formula_type_id

   AND con.context_id = fcu.context_id

   AND ft.base_formula_type_name = :p_formula_type_name

SQL Query to get the contexts of a DBI in Oracle Fusion

SELECT dbi.user_name

      ,base_context_name

  FROM ff_database_items_vl dbi

      ,ff_user_entities_vl userent

      ,ff_routes_vl routes

      ,ff_route_context_usages rcu

      ,ff_contexts_vl con

 WHERE dbi.user_entity_id = userent.user_entity_id

   AND userent.route_id = routes.route_id

   AND routes.route_id = rcu.route_id

   AND con.context_id = rcu.context_id

   AND dbi.base_user_name = :p_dbi_name

SQL Query to find all DBI's based on ANC Tables in Oracle Fusion

SELECT d.base_user_name dbi_name

      ,d.data_type dbi_data_type

      ,(SELECT listagg ('<' || rcu.sequence_no || ',' || c.base_context_name || '>', ',')

   within GROUP (ORDER BY rcu.sequence_no)

          FROM ff_route_context_usages rcu

              ,ff_contexts_b c

         WHERE rcu.route_id = r.route_id

           AND rcu.context_id = c.context_id

   ) route_context_usages

  FROM ff_database_items_b d

      ,ff_user_entities_b u

      ,ff_routes_b r

 WHERE UPPER(d.base_user_name) LIKE 'ANC%'

   AND d.user_entity_id = u.user_entity_id

   AND r.route_id = u.route_id

CLOB column splitting(one empty row and other actual data) data into multiple rows in Oracle Fusion

When working with CLOB columns in Fusion Reports we may face challenges like data splitting into multiple rows for the CLOB Column.

We faced challenges when generating report in Excel Output. The Description column showing one empty row followed by actual data as shown below.

Data Model Query:

When pasted in Excel File:

To overcome this issue convert clob value into Character using TO_CHAR function.

Ex: TO_CHAR(DESCRIPTION) 
here DESCRIPTION is a CLOB Column 

Thousand Separator((,)comma) in e-Text Report

AMOUNT column want to print thousand separator in Oracle Fusion e-Text Report use  Number, ###,###.00

Number, ###,###.00

R, ' '

AMOUNT

SupplierAddressImportTemplate.xlsm South Africa Suburb Field mapping in POZ_SUPPLIER_ADDRESSES_INT

Suburb mpping in Supplier Address Import Template will be mapped to Address Element Attribute2 (HZ_LOCATIONS.ADDR_ELEMENT_ATTRIBUTE2)

Query To Fetch AP Invoice Details From SO Number(Doc ID 2949013.1)

SELECT dh.source_order_number

      ,df.source_line_number as so_line_number

  ,df.fulfill_line_number 

  ,ddr.doc_user_key as po_number

  ,ai.invoice_num

  ,ail.quantity_invoiced

  ,ail.amount

  ,ail.line_number as ap_invoice_line_number

  FROM fusion.ap_invoices_all ai

      ,fusion.ap_invoice_lines_all ail

  ,fusion.doo_document_references ddr

  ,fusion.doo_headers_all dh

  ,fusion.doo_fulfill_lines_all df

 WHERE ai.invoice_id = ail.invoice_id

   AND dh.source_order_number = <sales_order_number>

   AND ddr.doc_id = TO_CHAR(ai.po_header_id)

   AND ddr.fulfill_line_id = df.fulfill_line_id

   AND df.header_id = dh.header_id

Query to get Item Category Details in Oracle Fusion

SELECT cba.bank_account_name

      ,cba.bank_account_id

      ,cba.bank_account_name_alt

      ,cba.bank_account_num

      ,cba.multi_currency_allowed_flag

      ,cba.zero_amount_allowed

      ,cba.account_classification

      ,cbb.bank_name

      ,cba.bank_id

      ,cbb.bank_number

      ,cbb.bank_branch_type

      ,cbb.bank_branch_name

      ,cba.bank_branch_id

      ,cbb.bank_branch_number

      ,cbb.eft_swift_code

      ,cbb.description bank_description

      ,cba.currency_code

      ,cbb.address_line1

  ,cbb.address_line2

      ,cbb.city

      ,cbb.county

      ,cbb.state

      ,cbb.zip_code

      ,cbb.country

      ,hou.name

      ,gcf.concatenated_segments

      ,cba.ap_use_allowed_flag

      ,cba.ar_use_allowed_flag

      ,cba.xtr_use_allowed_flag

      ,cba.pay_use_allowed_flag

  FROM ce_bank_accounts cba

      ,ce_bank_acct_uses_all bau

      ,cefv_bank_branches cbb

      ,hr_operating_units hou

      ,gl_code_combinations_kfv gcf

 WHERE cba.bank_account_id = bau.bank_account_id

   AND cba.bank_branch_id = cbb.bank_branch_id

   AND hou.organization_id = bau.org_id

   AND cba.asset_code_combination_id = gcf.code_combination_id

   AND (

cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE)

       )

   AND hou.name = 'XX Operating Unit'

ORDER BY cba.bank_account_num

Query for AR Sales Representative Name in Oracle Fusion

SELECT hp.party_name sales_representative

  FROM ra_customer_trx_all rcta

      ,jtf_rs_salesreps jrs

  ,hz_parties hp

 WHERE rcta.primary_resource_salesrep_id=jrs.resource_salesrep_id

   AND jrs.resource_id=hp.party_id

Join between Sales Order(SO) and Receivables(AR) in Oracle Fusion

SELECT *

  FROM ra_customer_trx_all rcta

      ,ra_customer_trx_lines_all ra

  ,doo_fulfill_lines_all l

 WHERE ra.interface_line_attribute5 = TO_CHAR(l.fulfill_line_id)

   AND rcta.customer_trx_id = ra.customer_trx_id

   AND rcta.trx_number = '123456789';

You can use INTERFACE_LINE_ATTRIBUTE5 column in RA_CUSTOMER_TRX_LINES_ALL which stores Fulfill Line ID of Sales Order.

Query to get AP Invoice Distribution to Inventory Material Distribution Data

SELECT mmt.transaction_date

          ,(SELECT ml.meaning

              FROM mfg_lookups ml

             WHERE ml.lookup_Type = 'CST_ACCOUNTING_LINE_TYPE'

               AND ml.lookup_code = mta.accounting_line_type

                ) accounting_type

          ,gl_code.concatenated_segments account, mtt.transaction_type_name transaction_type

          ,(SELECT po_hdr.segment1 

              FROM po_headers_all po_hdr 

             WHERE po_hdr.po_header_id = po_dist.po_header_id) transaction_source

          ,mmt.transaction_uom uom

          ,mta.primary_quantity 

          ,mta.base_transaction_value

          ,mmt.transaction_id, mmt.transaction_type_id, items.segment1 item, mmt.inventory_item_id, mmt.organization_id

          ,gl_code.code_combination_id

      FROM ap_invoice_distributions_all inv_dist

          ,po_distributions_all po_dist

          ,rcv_transactions rcv_trx

          ,mtl_material_transactions mmt

          ,mtl_transaction_types mtt

          ,mtl_transaction_accounts mta

          ,gl_code_combinations_kfv gl_code

          ,mtl_system_items_b items

     WHERE 1=1

       --AND inv_dist.po_distribution_id = 10536072

       AND inv_dist.po_distribution_id = po_dist.po_distribution_id

       AND po_dist.po_header_id = rcv_trx.po_header_id

       AND po_dist.po_line_id = rcv_trx.po_line_id

       AND po_dist.line_location_id = rcv_trx.po_line_location_id

       AND po_dist.po_distribution_id = rcv_trx.po_distribution_id

       AND transaction_type = 'DELIVER'

       AND mmt.rcv_transaction_id = rcv_trx.transaction_id

       AND mmt.transaction_type_id = mtt.transaction_type_id

       AND mmt.transaction_id = mta.transaction_id

       AND mta.reference_account = gl_code.code_combination_id

       AND mmt.inventory_item_id = items.inventory_item_id

       AND mmt.organization_id = items.organization_id

Query to get Requisition to PO Details in Oracle Fusion

SELECT DISTINCT PRHA.REQUISITION_NUMBER,

                PHA.SEGMENT1 PO_NUMBER,

                PAH.OBJECT_TYPE_CODE,

                PAH.OBJECT_SUB_TYPE_CODE,

                PAH.SEQUENCE_NUM,

                PPTF.FULL_NAME REQUESTER,

                PAH.ACTION_CODE,

                PAH.ACTION_DATE

  FROM POR_REQUISITION_HEADERS_ALL PRHA,

       POR_REQUISITION_LINES_ALL PRLA,

       POR_REQ_DISTRIBUTIONS_ALL PRDA,

       PO_HEADERS_ALL PHA,

       PO_LINES_ALL PLA,

       PO_DISTRIBUTIONS_ALL PDA,

       PO_ACTION_HISTORY PAH,

       PER_PERSON_NAMES_F PPTF

 WHERE 1 = 1

       AND PRHA.requisition_number IN

              ('REQ-12345')

       AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID

       AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID

       AND PRDA.DISTRIBUTION_ID = PDA.REQ_DISTRIBUTION_ID

       AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID

       AND PDA.PO_LINE_ID = PLA.PO_LINE_ID

       AND PRHA.REQUISITION_HEADER_ID = PAH.OBJECT_ID

       AND PPTF.PERSON_ID = PAH.PERFORMER_ID

       AND PPTF.name_type='GLOBAL'

       AND TRUNC(SYSDATE) BETWEEN PPTF.effective_start_date AND PPTF.effective_end_date

What is the difference between PER_ALL_ASSIGNMENTS_M and PER_ALL_ASSIGNMENTS_F in Oracle Fusion

PER_ALL_ASSIGNMENTS_M is the core table for assignments.
PER_ALL_ASSIGNMENTS_F is a view which stores effective latest change only.

The definition of the PER_ALL_ASSIGNMENTS_F view:

SELECT *

  FROM per_all_assignments_m

WHERE  EFFECTIVE_LATEST_CHANGE = 'Y';

If you want all the data then use the base table : PER_ALL_ASSIGNMENTS_M

If you want the effective latest change only, please use : PER_ALL_ASSIGNMENTS_F

Query to get Employee Leave Balance for Specific Plan in Oracle Fusion

SELECT papf.person_number employee_number

      ,ppn.full_name employee_name

  ,TO_CHAR(acc.procd_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') transaction_date

  ,SUM(acc.value) balance

  FROM per_all_people_f papf

  ,per_person_names_f ppn

  ,anc_per_acrl_entry_dtls acc

  ,anc_absence_plans_vl abpv

WHERE papf.person_id = ppn.person_id

  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

  AND TRUNC(SYSDATE) BETWEEN ppn.effective_start_date AND ppn.effective_end_date

  AND ppn.name_type = 'GLOBAL'

  AND acc.person_id = papf.person_id

  AND abpv.absence_plan_id = acc.pl_id

  AND abpv.name = 'XX Plan'

  AND papf.person_number = '123456789'--:p_person_number

GROUP BY papf.person_number 

      ,ppn.full_name 

Query to get Customer Details in Oracle Fusion

select party.party_id

      ,party.party_number

      ,party.party_name customer_name

      ,(select party_nm.attribute1 from HZ_PERSON_PROFILES party_nm where party_nm.party_id = party.party_id) person_name_global

      ,party.party_type 

      ,party.orig_system_reference party_orig_system_reference

      ,party.sales_account_id

      ,cust_account.cust_account_id

      ,cust_account.account_number 

      ,cust_account.customer_class_code

      ,cust_account.orig_system_reference cust_orig_system_reference

      ,cust_accts.cust_acct_site_id

      ,cust_accts.party_site_id

      ,cust_accts.orig_system_reference cust_site_orig_system_reference

      ,cust_accts.bill_to_flag

      ,cust_accts.ship_to_flag

      ,party_site.location_id

      ,party_site.party_site_number

      ,party_site.party_site_name

      ,party_site.orig_system_reference party_site_orig_system_reference

      ,site_use.site_use_id

      ,site_use.site_use_code

      ,site_use.primary_flag

      ,site_use.location

      ,site_use.orig_system_reference site_use_orig_system_reference

      ,ref_accts.bu_id

      ,ref_accts.ledger_id

      ,ledger.name ledger_name

      ,ref_accts.rev_ccid

      ,ref_accts.rec_ccid

      ,rev_gcc.segment1||'-'||rev_gcc.segment2||'-'||rev_gcc.segment3||'-'||rev_gcc.segment4||'-'||rev_gcc.segment5||'-'||rev_gcc.segment6||'-'||rev_gcc.segment7||'-'||rev_gcc.segment8 revenue_account

      ,rec_gcc.segment1||'-'||rec_gcc.segment2||'-'||rec_gcc.segment3||'-'||rec_gcc.segment4||'-'||rec_gcc.segment5||'-'||rec_gcc.segment6||'-'||rec_gcc.segment7||'-'||rec_gcc.segment8 receivable_account

from hz_parties party

    ,hz_cust_accounts cust_account

    ,hz_cust_acct_sites_all cust_accts

    ,hz_party_sites party_site

    ,hz_cust_site_uses_all site_use

    ,ar_ref_accounts_all ref_accts

    ,gl_code_combinations rev_gcc

    ,gl_code_combinations rec_gcc

    ,gl_ledgers ledger

where party_name = 'AYMAN ABDULMOHSEN AL-OJAIMI'

  and party.party_id = cust_account.party_id

  and cust_account.cust_account_id = cust_accts.cust_account_id

  and cust_accts.party_site_id = party_site.party_site_id

  and cust_accts.cust_acct_site_id = site_use.cust_acct_site_id

  --AND site_use.primary_flag = 'Y'

  AND ref_accts.source_ref_table(+) = 'HZ_CUST_SITE_USES_ALL'

  and ref_accts.source_ref_account_id(+) = site_use.site_use_id

  and ref_accts.rev_ccid = rev_gcc.code_combination_id(+)

  and ref_accts.rec_ccid = rec_gcc.code_combination_id(+)

  and ref_accts.ledger_id = ledger.ledger_id(+)

Script to update Supplier pay_group_lookup_code in Oracle APPS R12

DECLARE

lc_return_status  VARCHAR2(2000);

ln_msg_count      NUMBER;

ll_msg_data        LONG;

Ln_Vendor_Id      NUMBER;

Ln_Vendor_site_Id NUMBER;

ln_message_int    NUMBER;

Ln_Party_Id        NUMBER;

lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

BEGIN

-- Initialize apps session

fnd_global.apps_initialize(0, 50554, 200);

mo_global.set_policy_context('S',204);

mo_global.init('SQLAP');

 

Ln_Vendor_Id                          := 1;

Lrec_Vendor_Rec.pay_group_lookup_code := 'Employee';

ap_vendor_pub_pkg.update_vendor_public(p_api_version => 1

                                          ,x_return_status => lc_return_status

                                          ,x_msg_count => ln_msg_count

                                          ,x_msg_data => ll_msg_data

                                          ,p_vendor_rec => Lrec_Vendor_Rec

                                          ,p_Vendor_Id => Ln_Vendor_Id

  );

IF (lc_return_status <> 'S') 

THEN

IF ln_msg_count    >= 1 THEN

FOR v_index IN 1..ln_msg_count

LOOP

fnd_msg_pub.get (p_msg_index => v_index

                ,p_encoded => 'F'

                ,p_data => ll_msg_data

,p_msg_index_out => ln_message_int 

);

Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);

dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );

END LOOP;

END IF;

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('SQLERRM - '||SQLERRM );

ROLLBACK;

END

Script to update Supplier Type(ap_suppliers.vendor_type_lookup_code) in Oracle APPS R12

DECLARE

lc_return_status  VARCHAR2(2000);

ln_msg_count      NUMBER;

ll_msg_data        LONG;

Ln_Vendor_Id      NUMBER;

Ln_Vendor_site_Id NUMBER;

ln_message_int    NUMBER;

Ln_Party_Id        NUMBER;

lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

BEGIN

-- Initialize apps session

fnd_global.apps_initialize(0, 50554, 200);

mo_global.set_policy_context('S',204);

mo_global.init('SQLAP');

 

Ln_Vendor_Id                          := 1;

Lrec_Vendor_Rec.vendor_type_lookup_code := 'LOCAL';

ap_vendor_pub_pkg.update_vendor_public(p_api_version => 1

                                          ,x_return_status => lc_return_status

                                          ,x_msg_count => ln_msg_count

                                          ,x_msg_data => ll_msg_data

                                          ,p_vendor_rec => Lrec_Vendor_Rec

                                          ,p_Vendor_Id => Ln_Vendor_Id

  );

IF (lc_return_status <> 'S') 

THEN

IF ln_msg_count    >= 1 THEN

FOR v_index IN 1..ln_msg_count

LOOP

fnd_msg_pub.get (p_msg_index => v_index

                ,p_encoded => 'F'

                ,p_data => ll_msg_data

,p_msg_index_out => ln_message_int 

);

Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);

dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );

END LOOP;

END IF;

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('SQLERRM - '||SQLERRM );

ROLLBACK;

END

Script to update Supplier Tax Payer ID(AP_SUPPLIERS.NUM_1099) in Oracle APPS R12

DECLARE

lc_return_status  VARCHAR2(2000);

ln_msg_count      NUMBER;

ll_msg_data        LONG;

Ln_Vendor_Id      NUMBER;

Ln_Vendor_site_Id NUMBER;

ln_message_int    NUMBER;

Ln_Party_Id        NUMBER;

lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;

BEGIN

-- Initialize apps session

fnd_global.apps_initialize(0, 50554, 200);

mo_global.set_policy_context('S',204);

mo_global.init('SQLAP');

 

Ln_Vendor_Id                          := 1;

Lrec_Vendor_Rec.jgzz_fiscal_code := 'X123456789X';

ap_vendor_pub_pkg.update_vendor_public(p_api_version => 1

                                          ,x_return_status => lc_return_status

                                          ,x_msg_count => ln_msg_count

                                          ,x_msg_data => ll_msg_data

                                          ,p_vendor_rec => Lrec_Vendor_Rec

                                          ,p_Vendor_Id => Ln_Vendor_Id

  );

IF (lc_return_status <> 'S') 

THEN

IF ln_msg_count    >= 1 THEN

FOR v_index IN 1..ln_msg_count

LOOP

fnd_msg_pub.get (p_msg_index     => v_index

                ,p_encoded       => 'F'

,p_data          => ll_msg_data

,p_msg_index_out => ln_message_int 

);

Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);

dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );

END LOOP;

END IF;

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('SQLERRM - '||SQLERRM );

ROLLBACK;

END

API to Create Online Accounting for AP Payment in Oracle APPS R12 (ap_drilldown_pub_pkg.payment_online_accounting)

    PROCEDURE xx_payment_accounting(errbuf OUT VARCHAR2

   ,retcode           OUT VARCHAR2

   ,p_invoice_id    IN  NUMBER

   )

IS

CURSOR c1 

IS

SELECT DISTINCT aca.check_id, aca.check_number        

              FROM ap_invoices_all xlpi

      ,ap_invoice_payments_all aipa

  ,ap_checks_all aca

             WHERE xlpi.invoice_id   = aipa.invoice_id

               AND xlpi.invoice_id   = pin_invoice_id

               AND aipa.check_id     = aca.check_id

               AND ap_checks_pkg.get_posting_status(aca.check_id) = 'N';

   -- This condition is to pick invoices which are paid and unaccounted

   ;  

ln_processed_cnt    NUMBER := 0;

ln_failed_cnt       NUMBER := 0;  

lv_error_buf        VARCHAR2(4000);

ln_retcode          NUMBER; 

BEGIN

FOR rec_create_pmt_accounting IN c1

LOOP

BEGIN

fnd_file.put_line(fnd_file.log,'Check ID: '||rec_create_pmt_accounting.check_id);

ln_retcode   := NULL;

lv_error_buf := NULL;

ap_drilldown_pub_pkg.payment_online_accounting(p_check_id           => rec_create_pmt_accounting.check_id

  ,p_accounting_mode    => 'P'      

  ,p_errbuf             => lv_error_buf

  ,p_retcode            => ln_retcode

  ,p_calling_sequence   => 'XX_PAYMENT_ACCOUNTING'

  );

IF ln_retcode = 0 

THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id 

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounted Sucessfully'

);

ln_processed_cnt := ln_processed_cnt+1;

ELSIF ln_retcode = 1 THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounting ended in WARNING. Errbuf : '

||lv_error_buf

);

ln_processed_cnt := ln_processed_cnt+1;

ELSIF ln_retcode = 2 THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id 

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounting ended in ERROR. Errbuf : '

||lv_error_buf

);       

ln_failed_cnt := ln_failed_cnt +1;          

ELSE

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id 

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounting ended in ERROR. Errbuf : '

||lv_error_buf

||' Retcode: '

||ln_retcode

);

ln_failed_cnt := ln_failed_cnt +1;

END IF; 

COMMIT;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: ' 

||rec_create_pmt_accounting.check_id

||'. Payment Accounting failed with Unhandled Exception. Error Message:' 

||SQLERRM);

ln_failed_cnt := ln_failed_cnt + 1;

END;

END LOOP;

fnd_file.put_line ( fnd_file.log,'Processed Record Count: '

||ln_processed_cnt

||' Failed Record Count: '

||ln_failed_cnt);

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line ( fnd_file.log,'Main Error in xx_payment_accounting. Error Message: '||SQLERRM);

END xx_payment_accounting;

ap_checks_pkg.get_posting_status(check_id) to pick AP Invoice Payments which are not acconted.

Pass check_id from ap_checks_all (aca) to ap_checks_pkg.get_posting_status to pick invoice payments which are not accounted.

ap_checks_pkg.get_posting_status(aca.check_id)

INVALID_PATH: File location or filename was invalid(UTL_FILE) in Oracle APPS

1. Create Directory using below command

CREATE OR REPLACE directory XXDIR AS '/home/erp';

--Here change directory path(/home/erp) according to your path.

Give the required permissions to above path.

2. Connect to WinSCP/Putty and Create directory.

3. Now use the directory name in utl_file 

UTL_FILE.FOPEN('XXDIR ','file_name','R');

Package jdr_utils in OAF

Jdr_utils.listcustomizations:

 begin 

    dbms_output.put_line('***START***');

    Jdr_utils.listcustomizations('/oracle/apps/ce/bankaccounttransfer/webui/BankAccountTransferPG');

    dbms_output.put_line('***STOP***'); 

end;

• Searches Personalization’s for the OAF Page. We can find the Page URL (path) from about this page.

jdr_utils.listDocuments:- Similar to list customizations, it searches recursively, we do not specify the page name, but path.

exec jdr_utils.listDocuments('/oracle/apps/ce/bankaccounttransfer/webui/', true)

Jdr_utils.listContents:- Recursively finds all personalization's from a given path.

exec Jdr_utils.listContents ('/oracle/apps/ce/bankaccounttransfer/webui', true)

jdr_utils.DeleteDocument:- It deletes Personalization.
exec jdr_utils.DeleteDocument('/oracle/apps/eam/workorder/webui/customizations/site/0/EAM_EW_START_PAGE')