Link between PA tables and AP Invoice

SELECT peia.expenditure_item_id trans_id
       , ppa.segment1 project
       , pt.task_number task
       , pea.expenditure_group batch
       , pet.expenditure_category expend_type
       , pet.expenditure_type exp_type
       , peia.transaction_source tx_src
       , peia.expenditure_item_date exp_date
       , peia.creation_date
       , peia.raw_cost
       , peia.quantity
       , peia.request_id
       , aia.invoice_num
       , pv.vendor_name supplier
       , peia.request_id
       , pcdla.system_reference1 vendor_id
       , pcdla.system_reference2 inv_id
       , pcdla.system_reference3 inv_distr_line_num
    FROM pa.pa_expenditure_items_all peia
       , pa.pa_cost_distribution_lines_all pcdla
       , pa.pa_expenditures_all pea
       , pa.pa_projects_all ppa
       , pa.pa_tasks pt
       , pa.pa_expenditure_types pet
       , pa.pa_expenditure_comments pec
       , applsys.fnd_user fu
       , ap.ap_invoices_all aia
       , po.po_vendors pv
       , ap.ap_invoice_distributions_all aida
   WHERE peia.expenditure_item_id = pcdla.expenditure_item_id
     AND pcdla.system_reference2 = aia.invoice_id(+)
     AND aia.invoice_id = aida.invoice_id
     AND pcdla.system_reference3 = aida.distribution_line_number
     AND pcdla.system_reference1 = pv.vendor_id
     AND peia.expenditure_id = pea.expenditure_id(+)
     AND peia.project_id = ppa.project_id(+)
     AND peia.task_id = pt.task_id(+)
     AND peia.expenditure_type = pet.expenditure_type(+)
     AND peia.expenditure_item_id = pec.expenditure_item_id(+)
     AND ppa.project_id = pt.project_id
     AND peia.created_by = fu.user_id(+)
    -- and aia.invoice_id = 123456
     AND ppa.segment1 = '100003234'
ORDER BY peia.transaction_source
       , peia.creation_date DESC
       , pt.task_number DESC;

API to update ap_invoices_all table (AP_INVOICES_PKG.Update_Row)

DECLARE
   current_calling_sequence   varchar2 (2000);
BEGIN
   current_calling_sequence := 'AP_INVOICES_PKG.UPDATE_ROW -->';

   DBMS_OUTPUT.put_line ('start1');

   FOR rec IN (SELECT   l.ROWID, l.*
                 FROM   ap_invoices_all l
                WHERE   invoice_num = '700033994')
   LOOP
      DBMS_OUTPUT.put_line ('loop');

      AP_INVOICES_PKG.Update_Row (
         X_Rowid                         => rec.ROWID,
         X_Invoice_Id                    => rec.invoice_id,
         X_Last_Update_Date              => SYSDATE,   --rec.last_update_date,
         X_Last_Updated_By               => 4060,       --rec.last_updated_by,
         X_Vendor_Id                     => rec.vendor_id,
         X_Invoice_Num                   => rec.invoice_num,
         X_Invoice_Amount                => rec.invoice_amount,
         X_Vendor_Site_Id                => rec.vendor_site_id,
         X_Amount_Paid                   => rec.amount_paid,
         X_Discount_Amount_Taken         => rec.discount_amount_taken,
         X_Invoice_Date                  => rec.Invoice_Date,
         X_Source                        => rec.source,
         X_Invoice_Type_Lookup_Code      => rec.invoice_type_lookup_code,
         X_Description                   => rec.description,
         X_Batch_Id                      => rec.batch_id,
         X_Amt_Applicable_To_Discount    => rec.amount_applicable_to_discount,
         X_Tax_Amount                    => rec.Tax_Amount,
         X_Terms_Id                      => rec.terms_id,
         X_Terms_Date                    => '09-JAN-2015',          --SYSDATE,
         X_Payment_Method_Lookup_Code    => rec.Payment_Method_Lookup_Code,
         X_Goods_Received_Date           => rec.goods_received_date,
         X_Invoice_Received_Date         => rec.invoice_received_date,
         X_Voucher_Num                   => rec.voucher_num,
         X_Approved_Amount               => rec.approved_amount,
         X_Approval_Status               => rec.approval_status,
         X_Approval_Description          => rec.approval_description,
         X_Pay_Group_Lookup_Code         => rec.pay_group_lookup_code,
         X_Set_Of_Books_Id               => rec.set_of_books_id,
         X_Accts_Pay_CCId                => rec.accts_pay_code_combination_id,
         X_Recurring_Payment_Id          => rec.recurring_payment_id,
         X_Invoice_Currency_Code         => rec.invoice_currency_code,
         X_Payment_Currency_Code         => rec.payment_currency_code,
         X_Exchange_Rate                 => rec.exchange_rate,
         X_Invoice_Distribution_Total    => rec.Invoice_Distribution_Total,
         X_Payment_Amount_Total          => rec.payment_amount_total,
         X_Payment_Status_Flag           => rec.payment_status_flag,
         X_Posting_Status                => rec.posting_status,
         X_Authorized_By                 => rec.authorized_by,
         X_Attribute_Category            => rec.attribute_category,
         X_Attribute1                    => rec.attribute1,
         X_Attribute2                    => rec.attribute2,
         X_Attribute3                    => rec.attribute3,
         X_Attribute4                    => rec.attribute4,
         X_Attribute5                    => rec.attribute5,
         X_Vendor_Prepay_Amount          => rec.vendor_prepay_amount,
         X_Prepay_Flag                   => rec.Prepay_Flag,
         X_Base_Amount                   => rec.base_amount,
         X_Exchange_Rate_Type            => rec.exchange_rate_type,
         X_Exchange_Date                 => rec.exchange_date,
         X_Payment_Cross_Rate            => rec.payment_cross_rate,
         X_Payment_Cross_Rate_Type       => rec.payment_cross_rate_type,
         X_Payment_Cross_Rate_Date       => rec.payment_cross_rate_date,
         X_Pay_Curr_Invoice_Amount       => rec.pay_curr_invoice_amount,
         X_Vat_Code                      => rec.Vat_Code,
         X_Last_Update_Login             => rec.last_update_login,
         X_Original_Prepayment_Amount    => rec.original_prepayment_amount,
         X_Earliest_Settlement_Date      => rec.earliest_settlement_date,
         X_Attribute11                   => TO_CHAR (SYSDATE,
                                                     'DD-MON-RRRR HH24:MI:SS'), --rec.attribute11,
         X_Attribute12                   => rec.attribute12,
         X_Attribute13                   => rec.attribute13,
         X_Attribute14                   => rec.attribute14,
         X_Attribute6                    => rec.attribute6,
         X_Attribute7                    => rec.attribute7,
         X_Attribute8                    => rec.attribute8,
         X_Attribute9                    => rec.attribute9,
         X_Attribute10                   => rec.attribute10,
         X_Attribute15                   => rec.attribute15,
         X_Cancelled_Date                => rec.cancelled_date,
         X_Cancelled_By                  => rec.cancelled_by,
         X_Cancelled_Amount              => rec.cancelled_amount,
         X_Temp_Cancelled_Amount         => rec.temp_cancelled_amount,
         X_Exclusive_Payment_Flag        => rec.exclusive_payment_flag,
         X_Po_Header_Id                  => rec.po_header_id,
         X_Ussgl_Transaction_Code        => rec.Ussgl_Transaction_Code,
         X_Ussgl_Trx_Code_Context        => rec.Ussgl_Trx_Code_Context,
         X_Doc_Sequence_Id               => rec.doc_sequence_id,
         X_Doc_Sequence_Value            => rec.doc_sequence_value,
         X_Doc_Category_Code             => rec.doc_category_code,
         X_Freight_Amount                => rec.Freight_Amount,
         X_Expenditure_Item_Date         => rec.expenditure_item_date,
         X_Expenditure_Organization_Id   => rec.expenditure_organization_id,
         X_Expenditure_Type              => rec.expenditure_type,
         X_Pa_Default_Dist_Ccid          => rec.pa_default_dist_ccid,
         X_Pa_Quantity                   => rec.pa_quantity,
         X_Project_Id                    => rec.project_id,
         X_Project_Accounting_Context    => rec.Project_Accounting_Context,
         X_Task_Id                       => rec.task_id,
         X_Awt_Flag                      => rec.awt_flag,
         X_Awt_Group_Id                  => rec.awt_group_id,
         --   X_Pay_Awt_Group_Id              => NULL,
         X_Reference_1                   => rec.reference_1,
         X_Reference_2                   => rec.reference_2,
         X_Auto_Tax_Calc_Flag            => rec.Auto_Tax_Calc_Flag,
         X_Org_Id                        => rec.org_id,
         X_global_attribute_category     => rec.global_attribute_category,
         X_global_attribute1             => rec.global_attribute1,
         X_global_attribute2             => rec.global_attribute2,
         X_global_attribute3             => rec.global_attribute3,
         X_global_attribute4             => rec.global_attribute4,
         X_global_attribute5             => rec.global_attribute5,
         X_global_attribute6             => rec.global_attribute6,
         X_global_attribute7             => rec.global_attribute7,
         X_global_attribute8             => rec.global_attribute8,
         X_global_attribute9             => rec.global_attribute9,
         X_global_attribute10            => rec.global_attribute10,
         X_global_attribute11            => rec.global_attribute11,
         X_global_attribute12            => rec.global_attribute12,
         X_global_attribute13            => rec.global_attribute13,
         X_global_attribute14            => rec.global_attribute14,
         X_global_attribute15            => rec.global_attribute15,
         X_global_attribute16            => rec.global_attribute16,
         X_global_attribute17            => rec.global_attribute17,
         X_global_attribute18            => rec.global_attribute18,
         X_global_attribute19            => rec.global_attribute19,
         X_global_attribute20            => rec.global_attribute20,
         X_calling_sequence              => current_calling_sequence,
         X_gl_date                       => rec.gl_date,
         X_award_Id                      => rec.award_id,
         X_approval_iteration            => rec.approval_iteration,
         X_approval_ready_flag           => rec.approval_ready_flag,
         X_wfapproval_status             => rec.wfapproval_status,
         X_requester_id                  => rec.requester_id,
         X_DBI_UPDATED_FLAG              => 'N'
      );
      COMMIT;

      DBMS_OUTPUT.put_line ('after commit');
   END LOOP;

   DBMS_OUTPUT.put_line ('loop end');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('other exception'||sqlerrm);
END;
/

Query to findout CustomerNumber and SiteNumber from Order

SELECT
         bill_cust_acct.account_number "Billto Customer Number"           ,
         ship_cust_acct.account_number "Shipto Customer Number"           ,
         bill_party_site.party_site_number "BillTo Site Number" ,
         ship_party_site.party_site_number "ShipTo Site Number"
  FROM   oe_order_headers_all ooha,
         hz_cust_site_uses_all bill_csu,
         hz_party_sites bill_party_site,
         hz_loc_assignments bill_loc_assign,
         hz_locations bill_loc,
         hz_cust_acct_sites_all bill_acct_site,
         hz_parties bill_party,
         hz_cust_accounts bill_cust_acct,
         hz_cust_site_uses_all ship_csu,
         hz_party_sites ship_party_site,
         hz_loc_assignments ship_loc_assign,
         hz_locations ship_loc,
         hz_cust_acct_sites_all ship_acct_site,
         hz_parties ship_party,
         hz_cust_accounts ship_cust_acct
 WHERE       ooha.invoice_to_org_id = bill_csu.site_use_id(+)
         AND bill_csu.cust_acct_site_id = bill_acct_site.cust_acct_site_id(+)
         AND bill_acct_site.party_site_id = bill_party_site.party_site_id(+)
         AND bill_acct_site.cust_account_id =
               bill_cust_acct.cust_account_id(+)
         AND bill_loc.location_id(+) = bill_party_site.location_id
         AND bill_loc.location_id = bill_loc_assign.location_id(+)
         AND NVL (bill_acct_site.org_id, -99) =
               NVL (bill_loc_assign.org_id, -99)
         AND bill_cust_acct.party_id = bill_party.party_id
         AND ooha.ship_to_org_id = ship_csu.site_use_id(+)
         AND ship_csu.cust_acct_site_id = ship_acct_site.cust_acct_site_id(+)
         AND ship_acct_site.party_site_id = ship_party_site.party_site_id(+)
         AND ship_acct_site.cust_account_id =
               ship_cust_acct.cust_account_id(+)
         AND ship_loc.location_id(+) = ship_party_site.location_id
         AND ship_loc.location_id = ship_loc_assign.location_id(+)
         AND NVL (ship_acct_site.org_id, -99) =
               NVL (ship_loc_assign.org_id, -99)
         AND ship_cust_acct.party_id = ship_party.party_id
         AND ooha.order_number = 10063583

What is SERIALLY_REUSABLE Pragma?

The SERIALLY_REUSABLE pragma indicates that the package state is needed only for the duration of one call to the server (for example, an OCI call to the database or a stored procedure call through a database link). After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions. This pragma is appropriate for packages that declare large temporary work areas that are used only once in the same session.

The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.

Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, the database generates an error.

A SERIALLY_REUSABLE pragma can appear in the specification of a bodiless package, or in both the specification and body of a package. The pragma cannot appear only in the body of a package.

Example Creating a Serially Reusable Package:

 

CREATE PACKAGE pkg1 IS

   PRAGMA SERIALLY_REUSABLE;
   num NUMBER := 0;
   PROCEDURE init_pkg_state(n NUMBER);
   PROCEDURE print_pkg_state;
END pkg1;

/

CREATE PACKAGE BODY pkg1 IS

   PRAGMA SERIALLY_REUSABLE;
   PROCEDURE init_pkg_state (n NUMBER) IS
   BEGIN
      pkg1.num := n;
   END;
   PROCEDURE print_pkg_state IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('Num: ' || pkg1.num);
   END;
END pkg1;
/