Created By:AMARALAM
Created Date:14-09-2012
*********************************

SELECT oeha.ORDER_NUMBER
      ,oeha.header_id
      ,oeha.ORDERED_DATE
      ,oeha.ORDER_TYPE_ID
      ,oeha.PRICE_LIST_ID
      ,oeha.SHIP_FROM_ORG_ID "Organization_Id"
      ,oeha.SOLD_TO_ORG_ID   "Customer_Id"
      ,oeha.SALESREP_ID
      ,oeha.FLOW_STATUS_CODE "Order Status"
      ,oola.ORDERED_ITEM_ID
      ,oola.ORDERED_QUANTITY
      ,oola.SUBINVENTORY
      ,oola.LINE_NUMBER
      ,oola.FREIGHT_CARRIER_CODE
      ,oola.FREIGHT_TERMS_CODE
      ,oola.FLOW_STATUS_CODE "Line Status"
      ,wdd.DELIVER_TO_LOCATION_ID
      ,wdd.DELIVERED_QUANTITY
      ,wdd.CANCELLED_QUANTITY
      ,wdd.RELEASED_STATUS
      ,wda.TYPE "Delivery Type"
      ,wnd.DELIVERED_DATE
      ,wnd.NAME "Delivery Id"
      ,wnd.SHIP_METHOD_CODE
      ,wnd.STATUS_CODE
      ,rcta.TRX_NUMBER
      ,rcta.TRX_DATE
      ,rcta.CUSTOMER_TRX_ID
      ,rctla.QUANTITY_INVOICED
      ,apsa.AMOUNT_APPLIED
      ,gjb.DEFAULT_PERIOD_NAME  "Period Name"
      ,gjb.NAME                 "Batch Name"
      ,gjh.NAME                 "Journal Entry For"
      ,gjh.JE_SOURCE            "Source"
      ,NVL(gjl.ENTERED_CR,0)    "Entered Credit"
      ,NVL(gjl.ENTERED_DR,0)    "Entered Debit"
      ,NVL(gjl.ACCOUNTED_CR,0)  "Accounted Credit"
      ,NVL(gjl.ACCOUNTED_DR,0)  "Accounted Debit"
      ,gjh.CURRENCY_CODE        "Currency"
      ,gjh.STATUS               "Posting Status"
      ,gjh.DATE_CREATED         "Gl Transfor Date"
FROM oe_order_headers_all oeha,
     oe_order_lines_all oola,
     wsh_delivery_details wdd,
     wsh_delivery_assignments wda,
     wsh_new_deliveries wnd,
     ra_customer_trx_all rcta,
     ra_customer_trx_lines_all rctla,
     ra_cust_trx_line_gl_dist_all rgda,
     ar_payment_schedules_all apsa,
     xla_distribution_links xdl,
     xla_events xe,
     xla_ae_headers xah,
     xla_ae_lines xal,
     gl_import_references gir,
     gl_je_lines gjl,
     gl_je_headers gjh,
     gl_je_batches gjb
WHERE 1=1 AND
      (oeha.ORDER_NUMBER BETWEEN :ono1 AND :ono2 ) AND
      oeha.SOLD_TO_ORG_ID=:cust_id AND                      --1005
      oeha.HEADER_ID=oola.HEADER_ID AND
      oola.LINE_ID=wdd.SOURCE_LINE_ID AND
      wdd.DELIVERY_DETAIL_ID=wda.DELIVERY_DETAIL_ID AND
      wda.DELIVERY_ID=wnd.DELIVERY_ID AND
      to_char(oeha.ORDER_NUMBER)=rcta.INTERFACE_HEADER_ATTRIBUTE1 AND
      to_char(wnd.DELIVERY_ID)=rcta.INTERFACE_HEADER_ATTRIBUTE3 AND
      rcta.CUSTOMER_TRX_ID=rctla.CUSTOMER_TRX_ID AND
      to_char(oola.LINE_ID)=rctla.INTERFACE_LINE_ATTRIBUTE6 AND
      rctla.LINE_TYPE='LINE' AND
      rctla.CUSTOMER_TRX_LINE_ID=rgda.CUSTOMER_TRX_LINE_ID AND
      rcta.CUSTOMER_TRX_ID=apsa.CUSTOMER_TRX_ID AND
      rgda.CUST_TRX_LINE_GL_DIST_ID=xdl.SOURCE_DISTRIBUTION_ID_NUM_1 AND
      xdl.SOURCE_DISTRIBUTION_TYPE LIKE 'RA_CUST_TRX_LINE_GL_DIST_ALL' AND
      xdl.APPLICATION_ID=xe.APPLICATION_ID AND
      xdl.EVENT_ID=xe.EVENT_ID AND
      xdl.AE_HEADER_ID=xal.AE_HEADER_ID AND
      xdl.AE_LINE_NUM=xal.AE_LINE_NUM AND
      xe.APPLICATION_ID=xah.APPLICATION_ID AND
      xe.EVENT_ID=xah.EVENT_ID AND
      xah.APPLICATION_ID=xal.APPLICATION_ID AND
      xah.AE_HEADER_ID=xal.AE_HEADER_ID AND
      xal.GL_SL_LINK_ID=gir.GL_SL_LINK_ID AND
      xal.GL_SL_LINK_TABLE=gir.GL_SL_LINK_TABLE AND
      gir.JE_HEADER_ID=gjl.JE_HEADER_ID AND
      gir.JE_LINE_NUM=gjl.JE_LINE_NUM AND
      gjl.JE_HEADER_ID=gjh.JE_HEADER_ID AND
      gjh.JE_BATCH_ID=gjb.JE_BATCH_ID;


Ur's
AmarAlam

Customer Base Tables

AR_CUST_RECEIPT_METHODS_V	Stores the payment method information of  the customer
RA_TERMS	Contains the payment term details
AR_DUNNING_LETTER_SETS	Stores dunning letter master details
AR_STATEMENT_CYCLES	Statement cycle details
HZ_CUST_PROFILE_CLASSES	Master table for customer account profile classes
AR_COLLECTORS	Master table for collector information
AR_AUTOCASH_HIERARCHIES	Master table for hierarches details related to Lockbox program
GL_CODE_COMBINATIONS_KFV	Stores the account code combinations
HZ_PARTIES	Stores the party information
HZ_PARTY_SITES	Master table for party site
HZ_LOCATIONS	Master table for addresses
HZ_PARTY_SITES	Master table for party sites
HZ_CUST_ACCOUNTS	Master table for customer account details
HZ_CUST_ACCT_SITES_ALL	Master table for customer account sites
HZ_CUST_SITE_USES_ALL	Master table for customer account site uses
HZ_CUSTOMER_PROFILES	Master table for customer account/site profiles
HZ_CUST_PROFILE_AMTS	Master table for customer account/site profile amounts
HZ_CUST_ACCOUNT_ROLES	Master table for customer account/site contacts
HZ_ROLE_RESPONSIBILITIES	Master table for customer account/site contact responsibilities
HZ_RELATIONSHIPS	Master table for storing party relationship details
HZ_CODE_ASSIGNMENTS	Master table for party organization classifications
HZ_ORGANIZATION_PROFILES	Master table for storing the organization type party profiles
HZ_PERSON_PROFILES	Master table for storing the person type party profiles
HZ_ORG_CONTACTS	Master table for storing the organization contact details
HZ_ORG_CONTACT_ROLES	Master table for storing the organization contact roles responsibilities
HZ_CONTACT_POINTS	Master table for storing the party/party site contact point details

Difference Between Purchase Order Types

Oracle Purchasing provides the following purchase order types: Standard Purchase Order, Planned Purchase Order, Blanket Purchase Agreement and Contract Purchase Agreement. You can use the Document Name field in the Document Types window to change the names of these documents. For example, if you enter Regular Purchase Order in the Document Name field for the Standard Purchase Order type, your choices in the Type field in the Purchase Orders window will be Regular Purchase Order, Planned Purchase Order, Blanket Purchase Agreement and Contract Purchase Agreement.

Standard Purchase Orders:-

You generally create standard purchase orders for one–time purchase of various items. You create standard purchase orders when you know the details of the goods or services you require, estimated costs, quantities, delivery schedules, and accounting distributions. If you use encumbrance accounting, the purchase order may be encumbered since the required information is known.

Blanket Purchase Agreements (BPA):-

You create blanket purchase agreements when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them. BPA are widely used in product manufacturing companies.

You can issue a blanket release against a blanket purchase agreement to place the actual order (as long as the release is within the blanket agreement effectivety dates). If you use encumbrance accounting, you can encumber each release.

Contract Purchase Agreements:-

You create contract purchase agreements with your suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing. You can later issue standard purchase orders referencing your contracts, and you can encumber these purchase orders if you use encumbrance accounting.

Planned Purchase Order:-

You create a planned purchase order when you want to establish a long term agreement with a single source supplier with a commitment to buy goods or services. Planned purchase orders include tentative delivery schedules and accounting distributions. You then create scheduled releases against the planned purchase order to actually order the goods or services.

Ur's
AmarAlam

Benefits of Oracle XML Publisher

» Document Management Solution well integrated with Oracle Applications.
» A solution which authors, manages and delivers the Business documents.
» Meets the Business Requirements like corporate identity.
» Removes the Complexity
» Reduces Maintenance Cost
» Reduces Total Cost
» One Template -> Multiple Outputs -> PDF,HTML,EXCEL,RTF
» Business Consultants can use familiar desktop tools like Word, PDF, etc.
» Extract one time ->-> Publish multiple times

Query to get details of XML Report

SELECT fndcptl.USER_CONCURRENT_PROGRAM_NAME UserConcurrentProgram,
       fndcp.CONCURRENT_PROGRAM_NAME ConcurrentProgramShortName,
       fndex.EXECUTION_FILE_NAME ExecutionFileName,
       datadefinitiontl.DATA_SOURCE_NAME DataDefinitionName,
       datadefinition.DATA_SOURCE_CODE DataDefinitionCode,
       templatestl.TEMPLATE_NAME TemplateName,
       templates.TEMPLATE_CODE TemplateCode,
       lobs.FILE_NAME FileName
  FROM fnd_executables fndex,
       fnd_concurrent_programs fndcp,
       fnd_concurrent_programs_tl fndcptl,
       xdo_ds_definitions_b datadefinition,
       xdo_ds_definitions_tl datadefinitiontl,
       xdo_templates_b templates,
       xdo_templates_tl templatestl,
       xdo_lobs lobs
 WHERE     fndex.EXECUTABLE_ID = fndcp.EXECUTABLE_ID
       AND fndcp.CONCURRENT_PROGRAM_ID = fndcptl.CONCURRENT_PROGRAM_ID
       AND fndcp.CONCURRENT_PROGRAM_NAME = datadefinition.DATA_SOURCE_CODE
       AND datadefinition.DATA_SOURCE_CODE =
              datadefinitiontl.DATA_SOURCE_CODE
       AND datadefinitiontl.DATA_SOURCE_CODE = templates.DATA_SOURCE_CODE
       AND templates.TEMPLATE_CODE = templatestl.TEMPLATE_CODE
       AND templatestl.TEMPLATE_CODE = lobs.LOB_CODE
       AND lobs.LOB_TYPE = 'TEMPLATE_SOURCE'
       AND fndex.EXECUTABLE_NAME = 'XXAJ_SALES_ORDER_REPORT'


Ur's
AmarAlam

Some Useful XML Tags

FND_GLOBAL PACKAGE

FND_GLOBAL package gives the most of the enviroment values
THis package should be used in database side only...use FND_PROFILE package to get these values in Forms.
1.FND_GLOBAL.USERID --Returns userid

2.FND_GLOBAL.APPS_INTIALIZEprocedure APPS_INITIALIZE(user_id in number,resp_id in number,resp_appl_id in number);
This is used to set the values userid and responsibilityid for a session
3.FND_GLOBAL.LOGIN_ID -Gives login id
4.FND_GLOBAL.CONC_LOGIN_ID--Not sure how to use this
5.FND_GLOBAL.PROG_APPL_ID--Concurrent program application id
6.FND_GLOBAL.CONC_PROGRAM_ID--Concurrent program id
7.FND_GLOBAL.CONC_REQUEST_ID (Server)This will give the concurrent request id of the program which is calling the plsql package..

ln_Request_id:=FND_GLOBAL.CONC_REQUEST_ID;


Ur's
AmarAlam