JA India - Commercial Invoice - draft

Description
Categories: BI Publisher
Columns: Customer Trx Id, Line Number, Concatenated Segments, Item Tariff, Organization Id, Customer Id, Rate Per Unit, Excise Duty Rate, Duty Amount, Pan No2 ...
Application: Asia/Pacific Localizations
Source: India - Commercial Invoice Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINCINI_XML
DB package: JAINCINI
SELECT distinct
	a.customer_trx_id,
	C.LINE_NUMBER,
	D.CONCATENATED_SEGMENTS,
	E.ITEM_TARIfF,
	f.organization_id, F.CUSTOMER_ID, F.RATE_PER_UNIT, F.EXCISE_DUTY_rATE, F.DUTY_AMOUNT,
	H.PAN_NO PAN_NO2, H.EXCISE_DUTY_REG_NO, H.EXCISE_DUTY_RANGE ,
	H.EXCISE_DUTY_DIVISION , H.EXCISE_DUTY_COMM,H.ST_REG_NO ST_REG_NO2 ,H.CST_REG_NO CST_REG_NO2,
	r.party_name customer_name,
	SUBSTR(j.ADDRESS1,1,5), SUBSTR(j.ADDRESS2,1,5),
	SUBSTR(j.ADDRESS3,1,5), SUBSTR(j.ADDRESS4,1,5),
	C.QUANTITY_INVOICED, a.trx_number, a.trx_date,
	C.UOM_CODE, C.EXTENDED_AMOUNT,
	C.LINE_TYPE, C.CUSTOMER_TRX_LINE_ID,
	b.delivery_id
FROM RA_CUSTOMER_TRX_ALL A,
	JAI_OM_WSH_LINES_ALL b,
	RA_CUSTOMER_TRX_LINES_ALL C,
	MTL_SYSTEM_ITEMS_KFV D,
	JAI_INV_ITM_SETUPS E,
	JAI_CMN_RG_23D_TRXS F,
	hz_cust_site_uses_all  G,
	JAI_CMN_CUS_ADDRESSES H,
	HZ_LOCATIONS J, HZ_PARTY_SITES HZPS, HZ_CUST_ACCT_SITES_ALL HZCAS,
	hz_parties r, hz_cust_accounts hzca
WHERE  
	a.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID and
	a.created_from = 'RAXTRX'	and	
	b.delivery_id = to_number(a.interface_header_attribute3) and
	b.order_line_id = to_number(c.interface_line_attribute6) AND
	f.reference_LINE_ID = b.delivery_detail_id
	AND E.INVENTORY_ITEM_ID= B.INVENTORY_ITEM_ID
	AND E.ORGANIZATION_ID = B.ORGANIZATION_ID
	AND F.INVENTORY_ITEM_ID= B.INVENTORY_ITEM_ID
	AND F.ORGANIZATION_ID = B.ORGANIZATION_ID
	AND D.INVENTORY_ITEM_ID= B.INVENTORY_ITEM_ID
	AND D.ORGANIZATION_ID = B.ORGANIZATION_ID
	AND A.BILL_TO_CUSTOMER_ID = nvl(:p_customer_id, A.BILL_TO_CUSTOMER_ID)
	AND A.BILL_TO_CUSTOMER_ID = F.CUSTOMER_ID
  AND hzca.cust_account_id = f.customer_id
  AND r.party_id = hzca.party_id
	and b.organization_id = nvl(:p_org_id, b.organization_id)
	and b.location_id = nvl(:p_loc_id, b.location_id)
	and a.customer_trx_id = nvl(:p_invoice_no,a.customer_trx_id)
	AND G.SITE_USE_ID= F.SHIP_TO_SITE_ID
	AND G.cust_acct_site_id = H.ADDRESS_ID
	AND H.ADDRESS_ID= hzcas.cust_acct_site_id
  AND hzcas.party_site_id = hzps.party_site_id
  AND hzps.location_id = j.location_id 
 and f.organization_id=:organization_id1
 ORDER BY 16 ASC,15 ASC,23 ASC,24 ASC,13 ASC,14 ASC,12 ASC,11 ASC,17 ASC,6 ASC,10 ASC,5 ASC,20 ASC,19 ASC,21 ASC,18 ASC,1 ASC
Parameter Name SQL text Validation
Organization
 
LOV Oracle
Location
 
LOV Oracle
Customer
 
LOV Oracle
Order/Invoice
 
LOV Oracle
Invoice
 
LOV Oracle