ebs ar inovice sql

最新推荐文章于 2022-06-01 08:26:28 发布

Leon_2015

最新推荐文章于 2022-06-01 08:26:28 发布

阅读量398

点赞数

CC 4.0 BY-SA版权

分类专栏： EBS AR

本文链接：https://blog.youkuaiyun.com/lyyong2007/article/details/49999841

EBS 同时被 2 个专栏收录

13 篇文章

订阅专栏

3 篇文章

订阅专栏

本文介绍了一种从Oracle系统中同步出货与发票数据的方法。通过SQL查询结合特定业务逻辑，确保出货清单与发票信息的一致性和准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/*DECLARE
BEGIN
    fnd_global.apps_initialize(user_id           =>6045,
                               resp_id           => 20420,
                               resp_appl_id      =>1,
                               security_group_id => 0,
                               server_id         =>1 );
END;*/
--前提条件，同时出货的同一item价格必须一样，如果有不同，要及时调整数据层的问题
/*(p_organization_id number,p_item_id number,p_customer_item varchar2
,p_delivery_id number,p_price_list number) return number is*/

---

select &P_PACKING_LIST_NO INVOICE_NO,
               &P_PACKING_LIST_NO   PICKING_NO,
            xplV.delivery_id,
         -- xplV.customer_po PO_NUM,

           --xpl.quantity,
           xplV.customer_item item,
           xplV.item_id INVENTORY_ITEM_ID ,
           xplV.organization_id,
           xplV.customer_item_attribute8 Dpak_Transistors,
           XPLV.quantity,
           --invoice_info.Unit_Selling_Price unit,
           xxgem_it.get_unit_price(xplv.organization_id,xplv.item_id,xplv.customer_item,xplv.delivery_id,invoice_info.price_list_id) unit,
           invoice_info.Rat_Term_Name Payment_Term,
           invoice_info.Invoice_Currency_Code cur1,
           invoice_info.Invoice_Currency_Code T_cur,
           (invoice_info.Unit_Selling_Price*XPLV.quantity)    amount,
           invoice_info.bill_to_site_use_id,
           ' ' Bill_To_Name,
           ' ' BILL_TO_ADDRESS,
           ' ' PRICE_LIST_ID,
            5 CATEGORY_ID,
           ' ' Ship_To_Name,
           ' ' Ship_To_Address,
           ' ' so_line_id

      FROM (
            /* select rctl.INVENTORY_ITEM_ID, --
                   rctl.Unit_Selling_Price/&P_DISTCONT Unit_Selling_Price, --
                   Rctp.Rat_Term_Name, --
                   Rctp.Invoice_Currency_Code Invoice_Currency_Code, --
                   RCTP.bill_to_site_use_id, --? ooha.invoice_to_org_id
                   sum(rctl.Unit_Selling_Price) qyy ---
              from Ra_Customer_Trx_Lines     Rctl,
                   Ra_Customer_Trx_Partial_v Rctp
             where Rctp.Customer_Trx_Id = Rctl.Customer_Trx_Id
               and Rctl.interface_line_attribute6 in
                   (select wdd.source_line_id
                      from wsh_delivery_assignments wda, --yong.li
                           wsh_delivery_details     wdd ---yong.li 201202 13
                          ,
                           xxgem_packing_list_v     xplv
                     where wda.delivery_detail_id = wdd.delivery_detail_id
                       and wdd.organization_id = xplv.organization_id
                          -- and wdd.inventory_item_id=xpl.item_id
                       and wda.delivery_id = xplv.delivery_id
                          -- and wdd.inventory_item_id=xplv.item_id
                       and xplv.packing_list_no = &P_PACKING_LIST_NO

                    )
                  -- and rctl.INVENTORY_ITEM_ID = xplv.item_id
               and rctl.LINE_TYPE = 'LINE'
             group by rctl.INVENTORY_ITEM_ID,
                      rctl.Unit_Selling_Price,
                      Rctp.Rat_Term_Name,
                      Rctp.Invoice_Currency_Code,
                      RCTP.bill_to_site_use_id */


            select oola.inventory_item_id
                     ,oola.price_list_id
                    ,ooha.invoice_to_org_id bill_to_site_use_id
                   --,oola.payment_type_code
                   ,(SELECT AA.name FROM RA_TERMS_VL AA WHERE AA.term_id=ooha.payment_term_id ) Rat_Term_Name
                   ,oola.unit_selling_price
                   ,ooha.transactional_curr_code Invoice_Currency_Code
                   ,ooha.order_number
                   ,oola.ordered_item
                   ,oola.line_number
                   ,sum(oola.ordered_quantity)

            from oe_order_headers_all ooha ,
                 oe_order_lines_all    oola
            where ooha.header_id=oola.header_id
              and oola.line_id in ( select wdd.source_line_id
                      from wsh_delivery_assignments wda, --yong.li
                           wsh_delivery_details     wdd ---yong.li 201202 13
                          ,
                           xxgem_packing_list_v     xplv
                     where wda.delivery_detail_id = wdd.delivery_detail_id
                       and wdd.organization_id = xplv.organization_id
                          -- and wdd.inventory_item_id=xpl.item_id
                       and wda.delivery_id = xplv.delivery_id
                          -- and wdd.inventory_item_id=xplv.item_id
                       and xplv.packing_list_no = &P_PACKING_LIST_NO
                       )


           /* select rctl.INVENTORY_ITEM_ID, --
                   rctl.Unit_Selling_Price/&P_DISTCONT Unit_Selling_Price, --
                   Rctp.Rat_Term_Name, --
                   Rctp.Invoice_Currency_Code Invoice_Currency_Code, --
                   RCTP.bill_to_site_use_id, --? ooha.invoice_to_org_id
                   sum(rctl.Unit_Selling_Price) qyy ---
              from


                  Ra_Customer_Trx_Lines     Rctl,
                   Ra_Customer_Trx_Partial_v Rctp
             where Rctp.Customer_Trx_Id = Rctl.Customer_Trx_Id
               and Rctl.interface_line_attribute6 in
                   (select wdd.source_line_id
                      from wsh_delivery_assignments wda, --yong.li
                           wsh_delivery_details     wdd ---yong.li 201202 13
                          ,
                           xxgem_packing_list_v     xplv
                     where wda.delivery_detail_id = wdd.delivery_detail_id
                       and wdd.organization_id = xplv.organization_id
                          -- and wdd.inventory_item_id=xpl.item_id
                       and wda.delivery_id = xplv.delivery_id
                          -- and wdd.inventory_item_id=xplv.item_id
                       and xplv.packing_list_no = &P_PACKING_LIST_NO

                    )
                  -- and rctl.INVENTORY_ITEM_ID = xplv.item_id
               and rctl.LINE_TYPE = 'LINE'*/
             group by oola.INVENTORY_ITEM_ID,
                      oola.price_list_id,
                      oola.unit_selling_price,
                      ooha.payment_term_id,
                      ooha.transactional_curr_code,
                      ooha.invoice_to_org_id
                       ,ooha.order_number
                       ,oola.line_number
                       ,oola.ordered_item



                      ) invoice_info,



           (select xpl.delivery_id,
                   xpl.customer_po,
                   sum(xpl.quantity) quantity,
                   xpl.customer_item,
                   xpl.item_id,
                   xpl.organization_id,
                   xpl.customer_item_attribute8

              from xxgem_packing_list_v xpl
             where xpl.packing_list_no = &P_PACKING_LIST_NO
             group by xpl.delivery_id,
                      xpl.customer_po,
                      --xpl.quantity,
                      xpl.customer_item,
                      xpl.item_id,
                      xpl.organization_id,
                      xpl.customer_item_attribute8

            ) xplv
     WHERE INVOICE_INFO.INVENTORY_ITEM_ID = xplv.item_id