--AR INVOICE_X > Invoice Lines -- INVOICE LINE AMOUNTS --- select INVL.CUSTOMER_TRX_ID , INVL.CUSTOMER_TRX_LINE_ID , INVL.LINE_NUMBER , INVL.SALES_ORDER_LINE SO_LINE , INVL.INVENTORY_ITEM_ID ITEM_CODE , MTLS.DESCRIPTION ITEM_DESCRIPTION ,mtls.segment1 item_code2 , 'LINE' INV_LINE_TYPE -- , DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC , MTLS.DESCRIPTION INV_LINE_DESC , INVL.QUANTITY_INVOICED INV_QTY , INVL.UOM_CODE UOM , (INVL.QUANTITY_INVOICED * MTLS.UNIT_WEIGHT)/50 AT50_KG1 ,MTLS.UNIT_WEIGHT physicalwt ,MTLS.WEIGHT_UOM_CODE physicaluom , INVL.UNIT_SELLING_PRICE UNIT_SP , INVL.EXTENDED_AMOUNT , INVL.EXTENDED_AMOUNT LINE_AMOUNT , 0 LINE_DISCOUNT , 0 FREIGHT_AMOUNT , 0 TAX_AMOUNT , INVL.LINE_TYPE , INVL.ATTRIBUTE1 DR_NO , INVL.ATTRIBUTE2 ALT_UOM , TO_NUMBER(INVL.ATTRIBUTE3) ALT_QTY , 0 DISC_SEQ , NULL DISC_DESC , NULL DISC_CODE ,vats.printed_tax_name taxcode --ADDED ,itemast.inv_type TYPES1 ,itemast.seq_dpnd_class classes1 ,itemast.ship_class GROUPname1 ,itemast.attribute11 att11 ,itmsubt.tech_class_desc techd ,itemast.attribute10 pellet_crum_mash ,whsem.whse_code warehousen ,invl.INTERFACE_line_attribute3 OMDR --ADDED ABOVE from ic_whse_mst whsem, RA_CUSTOMER_TRX_LINES_ALL INVL , MTL_SYSTEM_ITEMS_B MTLS ,ar_vat_tax_all_tl vats --ADDED ,gmi_item_categories itmcat ,tech_cls_subcls_vw itmsubt ,ic_item_mst itemast --ADD ABOVE where invl.interface_line_attribute10=whsem.mtl_organization_id(+) and INVL.INVENTORY_ITEM_ID = MTLS.INVENTORY_ITEM_ID(+) AND MTLS.ORGANIZATION_ID=106 --ADDED AND MTLS.SEGMENT1= ITEMAST.ITEM_NO AND itemast.item_id=itmcat.item_id AND itmcat.CATEGORY_SET_ID=1100000047 AND itmcat.category_id=itmsubt.tech_category_id --ADDED ABOVE and INVL.EXTENDED_AMOUNT > 0 and INVL.LINE_TYPE = 'LINE' and invl.quantity_credited is null --and invl.quantity_ordered is not null and invl.vat_tax_id=vats.vat_tax_id(+) UNION -- INVOICE LINE DISCOUNTS --- select INVL.CUSTOMER_TRX_ID , INVL.CUSTOMER_TRX_LINE_ID , INVL.LINE_NUMBER , INVL.SALES_ORDER_LINE SO_LINE , INVL.INVENTORY_ITEM_ID ITEM_CODE , MTLS.DESCRIPTION ITEM_DESCRIPTION ,mtls.segment1 item_code2 , 'DISCOUNT' INV_LINE_TYPE --, DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC , MTLS.DESCRIPTION INV_LINE_DESC , 0 INV_QTY , INVL.UOM_CODE UOM , 0 AT50_KG1 ,MTLS.UNIT_WEIGHT physicalwt ,MTLS.WEIGHT_UOM_CODE physicaluom , 0 UNIT_SP , INVL.EXTENDED_AMOUNT , 0 LINE_AMOUNT , INVL.EXTENDED_AMOUNT LINE_DISCOUNT , 0 FREIGHT_AMOUNT , 0 TAX_AMOUNT , INVL.LINE_TYPE , INVL.ATTRIBUTE1 DR_NO , INVL.ATTRIBUTE2 ALT_UOM , 0 ALT_QTY , ORDA.PRICING_GROUP_SEQUENCE DISC_SEQ , ORDA.LIST_LINE_NO DISC_DESC , DECODE(ORDA.ARITHMETIC_OPERATOR, 'AMT', ' P'|| ORDA.OPERAND, ORDA.OPERAND ||'%') DISC_CODE ,' ' taxcode --ADDED ,itemast.inv_type TYPES1 ,itemast.seq_dpnd_class classes1 ,itemast.ship_class GROUPname1 ,itemast.attribute11 att11 ,itmsubt.tech_class_desc techd --ADDED ABOVE ,itemast.attribute10 pellet_crum_mash ,whsem.whse_code warehousen ,invl.INTERFACE_line_attribute3 OMDR from ic_whse_mst whsem, RA_CUSTOMER_TRX_LINES_ALL INVL , OE_PRICE_ADJUSTMENTS ORDA , MTL_SYSTEM_ITEMS_B MTLS --ADDED ,gmi_item_categories itmcat ,tech_cls_subcls_vw itmsubt ,ic_item_mst itemast --ADD ABOVE where invl.interface_line_attribute10=whsem.mtl_organization_id(+) and INVL.INVENTORY_ITEM_ID = MTLS.INVENTORY_ITEM_ID(+) AND MTLS.ORGANIZATION_ID=106 --ADDED AND MTLS.SEGMENT1= ITEMAST.ITEM_NO AND itemast.item_id=itmcat.item_id AND itmcat.CATEGORY_SET_ID=1100000047 AND itmcat.category_id=itmsubt.tech_category_id --ADDED ABOVE and INVL.EXTENDED_AMOUNT < 0 and invl.quantity_credited is null and INVL.LINE_TYPE = 'LINE' and INVL.INTERFACE_LINE_ATTRIBUTE6 = ORDA.LINE_ID(+) and INVL.INTERFACE_LINE_ATTRIBUTE11 = ORDA.PRICE_ADJUSTMENT_ID(+) UNION -- FREIGHT CHARGES --- select INVL.CUSTOMER_TRX_ID , INVL.CUSTOMER_TRX_LINE_ID , INVL.LINE_NUMBER , INVL.SALES_ORDER_LINE SO_LINE , INVL.INVENTORY_ITEM_ID ITEM_CODE , MTLS.DESCRIPTION ITEM_DESCRIPTION ,mtls.segment1 item_code2 , 'FREIGHT' INV_LINE_TYPE --, DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC , MTLS.DESCRIPTION INV_LINE_DESC , 0 INV_QTY ,' ' UOM , 0 AT50_KG1 ,MTLS.UNIT_WEIGHT physicalwt ,MTLS.WEIGHT_UOM_CODE physicaluom , 0 UNIT_SP , INVL.EXTENDED_AMOUNT , 0 LINE_AMOUNT , 0 LINE_DISCOUNT , INVL.EXTENDED_AMOUNT FREIGHT_AMOUNT , 0 TAX_AMOUNT , INVL.LINE_TYPE , INVL.ATTRIBUTE1 DR_NO , INVL.ATTRIBUTE2 ALT_UOM , TO_NUMBER(INVL.ATTRIBUTE3) ALT_QTY , 0 DISC_SEQ , NULL DISC_DESC , NULL DISC_CODE ,' ' taxcode ,' ' TYPES1 ,' ' classes1 ,' ' GROUPname1 ,' ' att11 ,' ' techd ,' ' pellet_crum_mash ,' ' warehousen ,' ' OMDR from RA_CUSTOMER_TRX_LINES_ALL INVL , MTL_SYSTEM_ITEMS_B MTLS where INVL.INVENTORY_ITEM_ID = MTLS.INVENTORY_ITEM_ID(+) and INVL.LINE_TYPE = 'FREIGHT' UNION -- TAXES --- select INVL.CUSTOMER_TRX_ID , INVL.CUSTOMER_TRX_LINE_ID , INVL.LINE_NUMBER , INVL.SALES_ORDER_LINE SO_LINE , INVL.INVENTORY_ITEM_ID ITEM_CODE , MTLS.DESCRIPTION ITEM_DESCRIPTION ,mtls.segment1 item_code2 , 'TAX' INV_LINE_TYPE -- , DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC , MTLS.DESCRIPTION INV_LINE_DESC , 0 INV_QTY , INVL.UOM_CODE UOM , 0 AT50_KG1 ,MTLS.UNIT_WEIGHT physicalwt ,MTLS.WEIGHT_UOM_CODE physicaluom , INVL.UNIT_SELLING_PRICE UNIT_SP , INVL.EXTENDED_AMOUNT , 0 LINE_AMOUNT , 0 LINE_DISCOUNT , 0 FREIGHT_AMOUNT , INVL.EXTENDED_AMOUNT TAX_AMOUNT , INVL.LINE_TYPE , INVL.ATTRIBUTE1 DR_NO , INVL.ATTRIBUTE2 ALT_UOM , TO_NUMBER(INVL.ATTRIBUTE3) ALT_QTY , 0 DISC_SEQ , NULL DISC_DESC , NULL DISC_CODE , ' ' taxcode --ADDED ,' ' TYPES1 ,' ' classes1 ,' ' GROUPname1 ,' ' att11 ,' ' techd ,' ' pellet_crum_mash ,' ' warehousen ,' ' OMDR from RA_CUSTOMER_TRX_LINES_ALL INVL , MTL_SYSTEM_ITEMS_B MTLS where INVL.INVENTORY_ITEM_ID = MTLS.INVENTORY_ITEM_ID(+) and INVL.LINE_TYPE = 'TAX' union --DM -- INVOICE LINE AMOUNTS --- select INVL.CUSTOMER_TRX_ID , INVL.CUSTOMER_TRX_LINE_ID , INVL.LINE_NUMBER , INVL.SALES_ORDER_LINE SO_LINE , 0 ITEM_CODE , ' ' ITEM_DESCRIPTION ,' ' item_code2 , 'LINE' INV_LINE_TYPE , DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC -- , MTLS.DESCRIPTION INV_LINE_DESC , INVL.QUANTITY_INVOICED INV_QTY , INVL.UOM_CODE UOM --, (INVL.QUANTITY_INVOICED * MTLS.UNIT_WEIGHT)/50 AT50_KG1 ,0 AT50_KG1 ,0 physicalwt ,' ' physicaluom , INVL.UNIT_SELLING_PRICE UNIT_SP , INVL.EXTENDED_AMOUNT , INVL.EXTENDED_AMOUNT LINE_AMOUNT , 0 LINE_DISCOUNT , 0 FREIGHT_AMOUNT , 0 TAX_AMOUNT , INVL.LINE_TYPE , INVL.ATTRIBUTE1 DR_NO , INVL.ATTRIBUTE2 ALT_UOM , TO_NUMBER(INVL.ATTRIBUTE3) ALT_QTY , 0 DISC_SEQ , NULL DISC_DESC , NULL DISC_CODE ,vats.printed_tax_name taxcode --ADDED ,' ' TYPES1 ,' ' classes1 ,' ' GROUPname1 ,' 'att11 ,' ' techd ,' ' pellet_crum_mash ,' ' warehousen ,' ' OMDR from RA_CUSTOMER_TRX_LINES_ALL INVL ,ar_vat_tax_all_tl vats where INVL.EXTENDED_AMOUNT > 0 and INVL.LINE_TYPE = 'LINE' and invl.quantity_ordered is null and invl.vat_tax_id=vats.vat_tax_id(+) -- cm ORDER union select INVL.CUSTOMER_TRX_ID , INVL.CUSTOMER_TRX_LINE_ID , INVL.LINE_NUMBER , INVL.SALES_ORDER_LINE SO_LINE , INVL.INVENTORY_ITEM_ID ITEM_CODE , MTLS.DESCRIPTION ITEM_DESCRIPTION ,mtls.segment1 item_code2 , 'LINE' INV_LINE_TYPE -- , DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC , MTLS.DESCRIPTION INV_LINE_DESC --, INVL.QUANTITY_INVOICED INV_QTY , INVL.QUANTITY_CREDITED INV_QTY , INVL.UOM_CODE UOM , (INVL.QUANTITY_CREDITED * MTLS.UNIT_WEIGHT)/50 AT50_KG1 -- ,0 AT50_KG1 ,MTLS.UNIT_WEIGHT physicalwt ,MTLS.WEIGHT_UOM_CODE physicaluom , INVL.UNIT_SELLING_PRICE UNIT_SP , INVL.EXTENDED_AMOUNT , INVL.EXTENDED_AMOUNT LINE_AMOUNT , 0 LINE_DISCOUNT , 0 FREIGHT_AMOUNT , 0 TAX_AMOUNT , INVL.LINE_TYPE , INVL.ATTRIBUTE1 DR_NO , INVL.ATTRIBUTE2 ALT_UOM , TO_NUMBER(INVL.ATTRIBUTE3) ALT_QTY , 0 DISC_SEQ , NULL DISC_DESC , NULL DISC_CODE ,' ' taxcode --ADDED ,' ' TYPES1 ,' ' classes1 ,' ' GROUPname1 ,' 'att11 ,' ' techd ,' ' pellet_crum_mash ,whsem.whse_code warehousen ,invl.INTERFACE_line_attribute3 OMDR from ic_whse_mst whsem, apps.ra_customer_trx_all invh ,apps.RA_CUSTOMER_TRX_LINES_ALL INVL ,apps.RA_CUST_TRX_TYPES_ALL ttype , apps.OE_PRICE_ADJUSTMENTS ORDA , apps.MTL_SYSTEM_ITEMS_B MTLS where invl.interface_line_attribute10=whsem.mtl_organization_id(+) and INVL.INVENTORY_ITEM_ID = MTLS.INVENTORY_ITEM_ID(+) and INVL.EXTENDED_AMOUNT < 0 and INVL.LINE_TYPE = 'LINE' and invh.CUSTOMER_TRX_ID = invl.CUSTOMER_TRX_ID and invh.CUST_TRX_TYPE_ID=ttype.CUST_TRX_TYPE_ID and ttype.type='CM' AND INVL.SALES_ORDER_SOURCE ='ORDER ENTRY' --xx and invl.quantity_credited is null and INVL.INTERFACE_LINE_ATTRIBUTE6 = ORDA.LINE_ID(+) and INVL.INTERFACE_LINE_ATTRIBUTE11 = ORDA.PRICE_ADJUSTMENT_ID(+) -- cm MANUAL union select INVL.CUSTOMER_TRX_ID , INVL.CUSTOMER_TRX_LINE_ID , INVL.LINE_NUMBER , INVL.SALES_ORDER_LINE SO_LINE , 0 ITEM_CODE , ' ' ITEM_DESCRIPTION ,' ' item_code2 , 'LINE' INV_LINE_TYPE , DECODE (INVL.DESCRIPTION, NULL, LINE_TYPE, INVL.DESCRIPTION) INV_LINE_DESC --, INVL.QUANTITY_INVOICED INV_QTY , INVL.QUANTITY_CREDITED INV_QTY , INVL.UOM_CODE UOM --, (INVL.QUANTITY_INVOICED * MTLS.UNIT_WEIGHT)/50 AT50_KG1 ,0 AT50_KG1 ,0 physicalwt ,' ' physicaluom , INVL.UNIT_SELLING_PRICE UNIT_SP , INVL.EXTENDED_AMOUNT , INVL.EXTENDED_AMOUNT LINE_AMOUNT , 0 LINE_DISCOUNT , 0 FREIGHT_AMOUNT , 0 TAX_AMOUNT , INVL.LINE_TYPE , INVL.ATTRIBUTE1 DR_NO , INVL.ATTRIBUTE2 ALT_UOM , TO_NUMBER(INVL.ATTRIBUTE3) ALT_QTY , 0 DISC_SEQ , NULL DISC_DESC , NULL DISC_CODE ,' ' taxcode --ADDED ,' ' TYPES1 ,' ' classes1 ,' ' GROUPname1 ,' 'att11 ,' ' techd , ' ' pellet_crum_mash ,' ' warehousen , ' ' OMDR from apps.ra_customer_trx_all invh ,apps.RA_CUSTOMER_TRX_LINES_ALL INVL ,apps.RA_CUST_TRX_TYPES_ALL ttype -- , apps.OE_PRICE_ADJUSTMENTS ORDA --, apps.MTL_SYSTEM_ITEMS_B MTLS where --INVL.INVENTORY_ITEM_ID = MTLS.INVENTORY_ITEM_ID(+) and INVL.EXTENDED_AMOUNT < 0 and INVL.LINE_TYPE = 'LINE' and invh.CUSTOMER_TRX_ID = invl.CUSTOMER_TRX_ID and invh.CUST_TRX_TYPE_ID=ttype.CUST_TRX_TYPE_ID and ttype.type='CM' AND INVL.SALES_ORDER_SOURCE IS NULL --xx and invl.quantity_credited is null -- and INVL.INTERFACE_LINE_ATTRIBUTE6 = ORDA.LINE_ID(+) -- and INVL.INTERFACE_LINE_ATTRIBUTE11 = ORDA.PRICE_ADJUSTMENT_ID(+) and invl. |