Find Responsibility name by Concurrent program name

SELECT DISTINCT
FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE
FRG.APPLICATION_ID =FAPP.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME = : Con_Pro_Name
 AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';

AP Invoice Aging Report Query.

SELECT   org_name,
              vendor_name,
              vendor_number,
              vendor_site_details,
              invoice_number,
              invoice_date,
              gl_Date,
              invoice_type,
              due_date,
              past_due_days,
              amt_due_remaining,
              CASE
                 WHEN past_due_days >= -999 AND past_due_days < 0
                 THEN
                    amt_due_remaining
                 ELSE
                    0
              END
                 CURRENT_BUCKET,
              CASE
                 WHEN past_due_days >= 0 AND past_due_days <= 30
                 THEN
                    amt_due_remaining
                 ELSE
                    0
              END
                 BUCKET_0_30,
              CASE
                 WHEN past_due_days > 30 AND past_due_days <= 60
                 THEN
                    amt_due_remaining
                 ELSE
                    0
              END
                 BUCKET_31_60,
              CASE
                 WHEN past_due_days > 60 AND past_due_days <= 90
                 THEN
                    amt_due_remaining
                 ELSE
                    0
              END
                 BUCKET_61_90,
              CASE
                 WHEN past_due_days > 90 AND past_due_days <= 120
                 THEN
                    amt_due_remaining
                 ELSE
                    0
              END
                 BUCKET_91_120,
              CASE
                 WHEN past_due_days > 120 AND past_due_days <= 999999
                 THEN
                    amt_due_remaining
                 ELSE
                    0
              END
                 GREATER_THAN_120
       FROM   (SELECT   hou.name org_name,
                        pv.vendor_name vendor_name,
                        pv.segment1 vendor_number,
                        pvs.vendor_site_code || ' ' || pvs.city || ' ' || state
                           vendor_site_details,
                        i.invoice_num invoice_number,
                        i.payment_status_flag,
                        i.invoice_type_lookup_code invoice_type,
                        i.invoice_date Invoice_Date,
                        i.gl_date Gl_Date,
                        ps.due_date Due_Date,
                        (CEIL (SYSDATE - ps.due_date)) past_due_days, -- DAYS_DUE,
                        DECODE (
                           i.invoice_currency_code,
                           'USD',
                           DECODE (
                              0,
                              0,
                              ROUND (
                                 ( (NVL (ps.amount_remaining, 0)
                                    / (NVL (i.payment_cross_rate, 1)))
                                  * NVL (i.exchange_rate, 1)),
                                 2
                              ),
                              ROUND( ( (NVL (ps.amount_remaining, 0)
                                        / (NVL (i.payment_cross_rate, 1)))
                                      * NVL (i.exchange_rate, 1))
                                    / 0)
                              * 0
                           ),
                           DECODE (
                              i.exchange_rate,
                              NULL,
                              0,
                              DECODE (
                                 0,
                                 0,
                                 ROUND (
                                    ( (NVL (ps.amount_remaining, 0)
                                       / (NVL (ps.payment_cross_rate, 1)))
                                     * NVL (i.exchange_rate, 1)),
                                    2
                                 ),
                                 ROUND( ( (NVL (ps.amount_remaining, 0)
                                           / (NVL (i.payment_cross_rate, 1)))
                                         * NVL (i.exchange_rate, 1))
                                       / 0)
                                 * 0
                              )
                           )
                        )
                           amt_due_remaining
                 FROM   ap_payment_schedules_all ps,
                        ap_invoices_all i,
                        ap_suppliers pv,
                        ap_supplier_sites_all pvs,
                        ap_lookup_codes alc1,
                        hr_operating_units hou
                WHERE       i.invoice_id = ps.invoice_id
                        AND i.vendor_id = pv.vendor_id
                        AND i.vendor_site_id = pvs.vendor_site_id
                        AND i.org_id = hou.organization_id
                        AND i.cancelled_date IS NULL
                        AND ps.amount_remaining = 0
                        AND (NVL (ps.amount_remaining, 0)
                             * NVL (i.exchange_rate, 1)) != 0
                        AND i.payment_status_flag IN ('N', 'P')
                        AND alc1.lookup_type(+) = 'INVOICE TYPE'
                        AND alc1.lookup_code(+) = i.invoice_type_lookup_code
                        --and    i.INVOICE_NUM ='358908411'
                        AND ap_invoices_pkg.get_approval_status (
                              i.invoice_id,
                              i.invoice_amount,
                              ps.payment_status_flag,
                              invoice_type_lookup_code
                           ) in('APPROVED','NEEDS REAPPROVAL'))
--                        AND i.org_id = fnd_profile.VALUE ('ORG_ID'))
   ORDER BY   2, 6;

How to find the Customer Remit-To Addresses.

Step 1: Go to AR responsibility
 


Step 2: Setup --> Print -->  Remit-To Addresses.




Step 3: Remit-To Addresses


To find country or operating unit vice the data.

Contingent Worker Functionality and Setup Steps For Release 12

This new functionality accessed features that had been incorporated into 11.5.10 for Purchasing and Human Resources, allowing the following actions to take place in the Oracle Applications:

• Define contingent workers in Oracle
• Assign contingent workers to tasks in Project Management
• Process labor costs of contingent workers in Project Costing
• Link and validate timecards against Purchase Orders
• Process contingent worker expenses in the same manner as employees

This new capability to handle contingent workers was possible due to changes across several Oracle Application modules:
New Purchase Order Line Types were added to allow the following capability:

• Specify individual named contractor start and end dates
• Define fixed price or rate based agreements with the contractor
• Specify price differentials on rate based agreements for items like overtime, weekend, holiday, travel and standby time
• Automatically generate purchase order receipts from timecards entered and approved through OTL

New HR person type allowed the following capability:

• Track contingent worker assignment history, similar to any regular employee
  • Periods of service
  • Breaks in assignments
  • Positions
  • Supervisors
  • Track worker and supplier relationships
    • Addition of the Supplier tab in the assignment record allowed the purchase order, purchase order line number, supplier name, supplier site and dates to be associated with the worker history

New OTL layouts for contingent workers supported the following capability:

• Association of time entry to purchase agreements
• Automation of timecard approval routings
• Generation of purchase order receipts from approved timecards
  • Run the concurrent request “Retrieve Time from OTL” with parameters to optionally select individual suppliers and dates

New iExpenses functionality supported the following capability:

• Entry of expense reports on behalf of contingent workers
• Automated approval routings
• Payments to contingent worker suppliers
  • Contingent workers are associated with a supplier in Accounts Payable
  • Import of the iExpense information creates an automatic AP Invoice for the supplier to pay for the incurred expense with the description including the identification of the contingent worker

Within Oracle Projects, there are no differences between regular employees and contingent workers for all the normal functions.  Both are:

• Assigned to projects and tasks
• Own tasks and deliverables
• Enter progress and record issues
• Any other normal project activity, limited only by the security functions defined

With the update of Oracle Applications and the Release 12 functionality, Oracle has modified some of the 11.5.10 capability and expanded capability.

• In R12.1.1 Oracle added a new flag to identify a contingency worker clearing project.  When the clearing project is used on a purchase order, the contingency worker can charge to any approved project when using OTL.  This enhances the ability to accommodate workers who are hired to work on multiple projects over the life of their engagement.

• In R12.1.2 Oracle added several new subcontractor management functions related to control of payments, tracking contract requirements, such as insurance certificates or other items that expire and need renewal over time.

As you can see from the breadth of impact contingent workers have on the applications, setup requirements can be difficult and require a broad range of knowledge to do so successfully.
Contingent Worker related features in Oracle E-Business Suite Applications, shown in a Release 12 screen below, include additional line types in Purchasing for fixed price or rate based workers.

Purchasing Line Types

Plus the addition of a tab to record the worker details, including name, start date and end date.

New Tab

Oracle Human Resources includes new person types for contingent workers and assignment status for active contingent workers, along with links to the supplier and the PO.

Contingent Worker Assignment

One significant change from 11.5.10 to Release 12 that impacts contingent workers is the elimination of pre-approved batches for expense reports.  This means expense reports will need to be entered through iExpense.  The AP Invoice Workbench functionality is NOT enabled for contingent workers due to the expense being paid to the supplier and not the individual.
Oracle E-Business Suite Projects applications treat contingent workers just like regular employees, allowing for scheduling, costing, reporting, and billing.  However, to ease reporting requirements it may be helpful to place contingent workers in a separate organization or assign them to a resource breakdown structure, which can make use of the person type assigned in HR.
For additional details on setting up contingent worker functionality in Release 11.5.10 M, please reference this white paper on our website: http://www.projectp.com/resources/pdfs/Contingent_Workers_Time_Entry.pdf

PO to AP invoice and hold details Query

 SELECT DISTINCT   ai.invoice_num,
                   ai.Invoice_date,
                   ai.Invoice_amount,
                   ail.line_number invoice_line_number,
                   ail.description,
                   ail.period_name,
                   ail.accounting_date,
                   ail.amount Invoice_line_Amount,
                   ail.quantity_invoiced,
                   pv.vendor_name,
                   pvs.vendor_site_code Vendor_SITE,
                   MAX (TRUNC (aha.hold_date)) HOLD_DATE,
                   MAX (TRUNC (aha.last_update_date)) Hold_Update_date,
                   DECODE (AHa.RELEASE_LOOKUP_CODE,
                           NULL, NULL,
                           AHa.LAST_UPDATE_DATE)
                      RELEASE_DATE,
                      aha.HOLD_LOOKUP_CODE HOLD_CODE,
                      aha.hold_reason,
                      ph.segment1 PO_NUMBER,
                      pl.line_num PO_LINE_NUMBER,
                      pl.quantity PO_LINE_QUANTITY,
                      pl.unit_price*pl.quantity PO_LINE_AMOUNT,
                  DECODE (PLT.MATCHING_BASIS,
                           'QUANTITY', PLL.QUANTITY,
                           'AMOUNT', PLL.AMOUNT)
                      SHIPMENT_ORDERED,
                   DECODE (PLT.MATCHING_BASIS,
                           'QUANTITY', PLL.QUANTITY_BILLED,
                           'AMOUNT', PLL.AMOUNT_BILLED)
                      SHIPMENT_BILLED,
                   DECODE (PLT.MATCHING_BASIS,
                           'QUANTITY', PLL.QUANTITY_RECEIVED,
                           'AMOUNT', PLL.AMOUNT_RECEIVED)
                      SHIPMENT_RECEIVED,
                      hrl.location_code,
                        ai.org_id Service_Line
              FROM AP_holds_all aha,
                   po_line_locations_all pll,
                   PO_HEADERS_ALL ph,
                   PO_LINES_ALL PL,
                   PO_LINE_TYPES PLT,
                   PO_DISTRIBUTIONS_ALL pda,
                   PO_VENDORS pv,
                   po_vendor_sites_all pvs,
                   AP_invoices_all ai,
                   AP_Invoice_lines_all ail,
                   AP_INVOICE_DISTRIBUTIONS_ALL aida,
                   ORG_ORGANIZATION_DEFINITIONS OOD,
                   HR_LOCATIONS_ALL_TL HRL
             WHERE     pll.line_location_id = aha.line_location_id
                   AND AHa.RELEASE_LOOKUP_CODE IS NULL
                   AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)
                   AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID(+)
                   AND pda.po_header_id = pl.po_header_id
                   AND pda.po_header_id = ph.po_header_id
                   AND ph.po_header_id = pl.po_header_id
                   and pda.po_line_id = pl.po_line_id
                   AND pda.po_distribution_id(+) = aida.po_distribution_id
                   and ail.line_number = aida.invoice_line_number
                   AND ail.invoice_id = aha.Invoice_id
                   AND ail.cancelled_flag = 'N'
                   AND ail.amount > '0'
                   AND ai.invoice_id = ail.Invoice_id
                   AND pv.vendor_id = ai.vendor_id
                   AND pv.vendor_id = pvs.vendor_id
                   AND pvs.vendor_site_id = ai.vendor_site_id
                   AND HRL.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID
                   AND OOD.ORGANIZATION_ID(+) = PLL.SHIP_TO_ORGANIZATION_ID
                   and aha.org_id in ( :1)
                   AND ai.invoice_num in (:2 )
          GROUP BY aha.invoice_id,
                   aha.line_location_id,
                   aha.HOLD_LOOKUP_CODE,
                   aha.hold_reason,
                   aha.release_lookup_code,
                   aha.release_reason,
                   ai.invoice_num,
                   ai.Invoice_amount,
                   ai.Invoice_date,
                   ail.invoice_id,
                   ail.line_number,
                   ail.description,
                   ail.period_name,
                   ail.accounting_date,
                   ail.amount,
                   ail.quantity_invoiced,
                   pv.vendor_name,
                  pvs.vendor_site_code,
                  ph.segment1,
                      pl.line_num ,
                      pl.quantity,
                      pl.unit_price*pl.quantity ,
                      ai.org_id,
                      hrl.location_code,
                   DECODE (AHa.RELEASE_LOOKUP_CODE,
                           NULL, NULL,
                           AHa.LAST_UPDATE_DATE),
                   DECODE (PLT.MATCHING_BASIS,
                           'QUANTITY', PLL.QUANTITY,
                           'AMOUNT', PLL.AMOUNT),
                   DECODE (PLT.MATCHING_BASIS,
                           'QUANTITY', PLL.QUANTITY_BILLED,
                           'AMOUNT', PLL.AMOUNT_BILLED),
                   DECODE (PLT.MATCHING_BASIS,
                           'QUANTITY', PLL.QUANTITY_RECEIVED,
                           'AMOUNT', PLL.AMOUNT_RECEIVED)
                   order by 2,1,4, 18