GL ar常用的table

select *  from mtl_parameters mp -- where mp.o
begin
 fnd_client_info.set_org_context(102); --125 org_id=102
end;


begin 
dbms_application_info.set_client_info(FND_PROFILE.VALUE('ORG_ID'));
end;
---期间表
select *  from ORG_ACCT_PERIODS oap where oap.acct_period_id=150006
SELECT * FROM RA_CUSTOMER_TRX_PARTIAL_V A
ORDER BY  A.created_by  DESC ;
SELECT ROWID,CUSTOMER_TRX_ID,INITIAL_CUSTOMER_TRX_ID 
FROM RA_CUSTOMER_TRX WHERE (CUSTOMER_TRX_ID='1499782');


BEGIN
  FOR REC IN (SELECT K.je_header_id ,
             K.LINE_JE_LINE_NUM
                FROM GL_JE_JOURNAL_LINES_V K
                WHERE  K.set_of_books_id = 1001
   AND K.batch_status || '' = 'P'
   AND (K.line_code_combination_id = 1020 AND K.line_period_name LIKE '%-11'
   )
   AND (K.currency_code = 'USD')
   AND (K.actual_flag = 'A')) LOOP
  -- INSERT INTO A20120106_TMP
    SELECT TRX_CLASS_NAME,
       TRX_TYPE_NAME,
       TRX_NUMBER_DISPLAYED,
       XLA_AEL_GL_V.TRX_DATE,
       COMMENTS,
       XLA_AEL_GL_V.DOC_SEQUENCE_NAME,
       XLA_AEL_GL_V.DOC_SEQUENCE_VALUE,
       ACCT_LINE_TYPE_NAME,
       ENTERED_DR,
       ENTERED_CR,
       ACCOUNTED_DR,
       ACCOUNTED_CR,
       THIRD_PARTY_NAME,
       THIRD_PARTY_NUMBER,
       THIRD_PARTY_SUB_NAME,
       ACCOUNTING_DATE,
       GL_TRANSFER_STATUS_NAME
  FROM XLA_AR_INV_AEL_GL_V XLA_AEL_GL_V
 WHERE application_id = 222
   AND REC.je_header_id = XLA_AEL_GL_V.je_header_id
   AND REC.LINE_JE_LINE_NUM = XLA_AEL_GL_V.je_line_num;
   
  -- COMMIT;
 END LOOP;
END;












 ---XXGEM Invoice Print (A4)_NEW   XXFINR_IVC_A4   main sql
 
 Select --Rctp.Rac_Bill_To_Customer_Name Bill_To_Name, 
Rctp.raa_bill_to_address1 Bill_To_Name, 
------
nvl(OOLA.PRICE_LIST_ID,0) PRICE_LIST_ID,
RCTL.INVENTORY_ITEM_ID,
mic1.CATEGORY_ID,
rctp.bill_to_site_use_id,
-------
      Decode(Instr(Rctp.Trx_Number, '-'), 0, Rctp.Trx_Number,
              Substr(Rctp.Trx_Number, 1, Instr(Rctp.Trx_Number, '-') - 1)) Invoice_No,
       Rctp.Rac_Ship_To_Customer_Name Ship_To_Name,
       Rctp.Raa_Ship_To_Address1 Ship_To_Address,
       Rctp.Rat_Term_Name Payment_Term,
       Mic.Segment2 Dpak_Transistors,
       --Msib.Segment1 Item,
                         Rctl.interface_line_attribute9 Item,
       Sum(Nvl(Rctl.Quantity_Invoiced, 0)) Quantity,
       Rctl.Unit_Selling_Price Unit,
       Rctp.Invoice_Currency_Code Cur,
       Rctp.Invoice_Currency_Code t_Cur,
       Sum(Nvl(Rctl.Quantity_Invoiced, 0) * Nvl(Rctl.Unit_Selling_Price, 0)) Amount,
       Rctp.Waybill_Number Picking_No,
       Oola.Cust_Po_Number Po_Num
From   Ra_Customer_Trx_Partial_v Rctp,
       Ra_Customer_Trx_Lines     Rctl,
       Mtl_System_Items_b        Msib,
       Oe_System_Parameters      Osp,
       Mtl_Item_Categories_v     Mic,
       Mtl_Item_Categories_v     Mic1,
       Oe_Order_Headers_All      Ooha,
       Oe_Order_Lines_All        Oola
Where  Rctp.Customer_Trx_Id = Rctl.Customer_Trx_Id
       And Rctl.Line_Type = 'LINE'
       And Rctl.Inventory_Item_Id = Msib.Inventory_Item_Id
       And Osp.Master_Organization_Id = Msib.Organization_Id
       And Msib.Inventory_Item_Id = Mic.Inventory_Item_Id
       And Msib.Organization_Id = Mic.Organization_Id
       And Mic.Category_Set_Name = 'Cost'
       and mic1.CATEGORY_SET_ID = 5
       and Mic1.Inventory_Item_Id = Msib.Inventory_Item_Id
       and Mic1.Organization_Id = Msib.Organization_Id
       And Rctp.Trx_Number Like  'J0000158%'   --:P_TRANSACTION_NUM||'%'
       And Oola.Line_Id(+) = Rctl.Interface_Line_Attribute6
      --And oola.Freight_Terms_Code = 'EXPORT'
       And Ooha.Header_Id(+) = Oola.Header_Id
Group  By --Rctp.Rac_Bill_To_Customer_Name,
          Rctp.raa_bill_to_address1,
          ------
          nvl(OOLA.PRICE_LIST_ID,0),
          RCTL.INVENTORY_ITEM_ID,
          mic1.CATEGORY_ID,
          rctp.bill_to_site_use_id,
          ------
          Decode(Instr(Rctp.Trx_Number, '-'), 0, Rctp.Trx_Number,
                 Substr(Rctp.Trx_Number, 1, Instr(Rctp.Trx_Number, '-') - 1)),
          Rctp.Trx_Date,
          Rctp.Rac_Ship_To_Customer_Name,
          Rctp.Raa_Ship_To_Address1,
          Rctp.Rat_Term_Name,
          Mic.Segment2,
          --Msib.Segment1,
                                        Rctl.interface_line_attribute9,
          Rctl.Unit_Selling_Price,
Rctp.Invoice_Currency_Code,
Rctp.Waybill_Number,
Oola.Cust_Po_Number,
Msib.Inventory_Item_Id,
Msib.Organization_Id
;
--XXAR_TRX_NUM valu set
SELECT *  FROM RA_CUSTOMER_TRX_ALL --RA_CUSTOMER_TRX


where trx_number in(
select Distinct Decode(Instr(Rct.Trx_Number, '-'), 0, Rct.Trx_Number,
              Substr(Rct.Trx_Number, 1, Instr(Rct.Trx_Number, '-') - 1))
from RA_CUSTOMER_TRX Rct
,ra_batch_sources    rbs
where rbs.batch_source_id   = rct.batch_source_id
and rct.complete_flag = 'Y' )
order by trx_number,customer_trx_id;