Thursday 28 December 2017

Highlight Data in different colors Using CSS

Use the below code in report query to highlight data with different colors based on conditions

CASE
            WHEN (TRUNC(SYSDATE) > date_down AND date_down IS NOT NULL)
            AND date_out IS NULL
               THEN 'Y'                               ---------------RED COLOR
            WHEN (TRUNC(SYSDATE) > date_out AND date_out IS NOT NULL)
            AND complete_date IS NULL
               THEN 'Z'                               -----------GREEN COLOR
            WHEN (TRUNC(SYSDATE) > complete_date AND complete_date IS NOT NULL)
            AND date_start IS NULL
               THEN 'X'                               --------ORANGE COLOR

            WHEN (    TRUNC(SYSDATE) > date_start
                  AND date_start IS NOT NULL
                  AND ship_date IS NULL
                 )
            AND date_complete IS NULL
               THEN 'P'                               --------YELLOW COLOR
            WHEN ((ship_date - TRUNC (SYSDATE) <= 2) AND ship_date IS NOT NULL
                 )
            AND date_complete IS NULL
               THEN 'Q'                               ---------BLUE COLOR

           

ELSE 'N'                                 ---------GREY COLOR


         END css_style

Use the below Javascript code for coloring.

//function to set css-bg color
function fn_css()
{
var i = 1;
var b,a,c,f;
while (i != 0) {
b = "000" + i;
b = pad(i, 4);
a = $('#f20_'+b).val(); //ID of CSS_STYLE column
c = "#f08_"+b; //ID of column to be highlighted
f = "f08_"+b;//date_out
g = "f10_"+b;//complete
h= "f12_"+b;//date_start
k= "f13_"+b;//date_complete
if (typeof a === "undefined") {
i=0;
}
else
{
//alert(g);
if (a == 'Y') {
/*Appying bg color to the closest row with ID f05_0001(first row, if 2nd row - f05_0002 and so on ) */
$(c).closest("tr").find('input[id="'+f+'"]').css("background-color","red");
}
else if(a == 'Z') {
$(c).closest("tr").find('input[id="'+g+'"]').css("background-color","green");
}
else if(a == 'X') {
$(c).closest("tr").find('input[id="'+h+'"]').css("background-color","orange");
}
else if(a == 'P') {
$(c).closest("tr").find('input[id="'+k+'"]').css("background-color","yellow");
}
else if(a == 'Q') {
$(c).closest("tr").children("td").css("background-color","blue");
}
i= i+1;
}
}
}

Check Data type using JavaScript

Use the below script to perform duplicate data validation along with numeric data type validation in Javascript.

<!--duplicate checking for lpn -->
<script>
function check_lpn(pThis)
{
var j_item_id=pThis.id;
var k_item_id='#f02'+j_item_id.substr(3);
//alert(j_item_id);
//alert(k_item_id);
var h='#'+j_item_id;
//alert(h);
var a= $(h).val();
var g=$(k_item_id).val();
//alert(a);
//alert(g);
if(isNaN(a)){
alert('The LPN is not numeric.Kindly enter numeric values');
$(h).focus();
}
else{
if (g)
{
var get = new htmldb_Get(null, $v('pFlowId'),'APPLICATION_PROCESS=Duplicate_LPN_check',$v('pFlowStepId'));
get.add('AI_LPN',a);
//get.add('AI_SHIPPING_ID',g);
ret = get.get();
//alert(ret);
if (ret != 0)
{
var get = new htmldb_Get(null, $v('pFlowId'),'APPLICATION_PROCESS=Duplicate_LPN_Shipping_id',$v('pFlowStepId'));
get.add('AI_LPN',a);
ret = get.get();
if(ret != g)
{
alert('The LPN is already present.Kindly select another LPN');
$(h).val(null);
$(h).focus();
}
}
}
else
{
var i = 1;
var j=0;
var b,a,c,f;
while (i != 0) {
b = "000" + i;
b = pad(i, 4);
d = $('#f05_'+b).val();
//alert(d);
//alert(typeof d);
if (typeof d === "undefined") {
i=0;
}
else
{
if(a == d)
{j= j+1;
}
if(j>1)
{
alert('The LPN is already present.Kindly select another LPN');
$(h).val(null);
$(h).focus();
break;
}
i= i+1;
}
}
}
}
}
</script>


Check duplicate data using Javascript

Use the below Javascript to identify duplicate data - In this sample code, when user enters order number in the tabular form, an error will be thrown if the order number already exists.

<!--duplicate checking for order no -->
<script>
function check_order_no(pThis)
{
var j_item_id=pThis.id;
var k_item_id='#f02'+j_item_id.substr(3);
//alert(j_item_id);
//alert(k_item_id);
var h='#'+j_item_id;
//alert(h);
var a= $(h).val();
var g=$(k_item_id).val();
//alert(a);
//alert(g);

if (g)
{
var get = new htmldb_Get(null, $v('pFlowId'),'APPLICATION_PROCESS=Duplicate_Order_no_check',$v('pFlowStepId'));
get.add('AI_ORDER_NO',a);
//get.add('AI_SHIPPING_ID',g);
ret = get.get();
//alert(ret);
if (ret != 0)
{
var get = new htmldb_Get(null, $v('pFlowId'),'APPLICATION_PROCESS=Duplicate_Order_no_shipping_id',$v('pFlowStepId'));
get.add('AI_ORDER_NO',a);
ret = get.get();
if(ret != g)
{
alert('The Order No is already present.Kindly select another Order No');
$(h).val(null);
$(h).focus();
}
}
}
else
{
var i = 1;
var j=0;
var b,a,c,f;
while (i != 0) {
b = "000" + i;
b = pad(i, 4);
d = $('#f03_'+b).val();
//alert(typeof d);
if (typeof d === "undefined") {
i=0;
}
else
{
if(a == d)
{j= j+1;
}
if(j>1)
{
alert('The Order No is already present.Kindly select another Order No');
$(h).val(null);
$(h).focus();
break;
}
i= i+1;
}
}
}
}
</script>

Monday 30 October 2017

ISO Receipt Register -GST Query

SELECT shipment_num, shipped_date, stnno, receipt_num, rcvd_date, send_orgid,
       send_orgname, recv_orgid, recv_org_name, gst_no, segment1, description,
       lot_num, pri_rec_uom, uomcode, LINK, rcv_qty, iso_price, iso_price_uom,
       NVL (tax_amount, 0) tax_amount,
       ((rcv_qty * iso_price) + NVL (tax_amount, 0)) stnvalue, option1, lrno,
       lrdate, freight
  FROM (SELECT shipment_num, shipped_date, stnno, receipt_num, rcvd_date,
               send_orgid, send_orgname, recv_orgid, recv_org_name, gst_no,
               segment1, description, lot_num, pri_rec_uom, uomcode, rcv_qty,
               iso_price_uom,
               CASE
                  WHEN
                      --NVL(iso_price_uom, 'NA') <> NVL(UOMCODE, 'NA') AND
                      NVL (iso_price_uom, 'NA') =
                                                NVL (uommain, 'NA')
                     THEN iso_price / LINK
                  ELSE iso_price
               END iso_price,
               tax_amount,
               CASE
                  WHEN UPPER (:opt1) = 'SENDORG'
                     THEN send_orgname
                  WHEN UPPER (:opt1) = 'SHIPNO'
                     THEN shipment_num
                  WHEN UPPER (:opt1) = 'RCPTNO'
                     THEN receipt_num
                  WHEN UPPER (:opt1) = 'RCPTDATE'
                     THEN TO_CHAR (rcvd_date, 'dd-mon-yyyy')
               END option1,
               lrno, lrdate, freight, LINK
          FROM (SELECT a.shipment_num, a.receipt_num,
                       c.transaction_date rcvd_date,
                       a.ship_to_org_id recv_orgid, e.NAME recv_org_name,
                       i.gst_no, f.organization_id send_orgid,
                       f.NAME send_orgname, a.shipped_date, d.segment1,
                       d.description, g.primary_quantity rcvd_qty, g.lot_num,
                       c.quantity rcv_qty, c.unit_of_measure rec_uom,
                       c.primary_unit_of_measure pri_rec_uom,
                       a.shipment_header_id, b.shipment_line_id,
                       c.transaction_id, b.requisition_line_id,
                       b.item_id AS inventory_item_id, h.LINK, h.uomcode,
                       i.tax_amount, h.uommain
                  FROM rcv_shipment_headers a,
                       rcv_shipment_lines b,
                       rcv_transactions c,
                       (SELECT inventory_item_id, segment1, description
                          FROM mtl_system_items
                         WHERE organization_id = 83) d,
                       hr_all_organization_units e,
                       (SELECT a.vat_reg_no tin_no, a.organization_id,
                               b.location_code NAME, a.location_id
                          FROM jai_cmn_inventory_orgs a,
                               hr_locations b,
                               hr_all_organization_units c
                         -- ja_in_hr_organization_units changed for R12
                        WHERE  a.organization_id = b.inventory_organization_id
                           AND b.inactive_date IS NULL
                           AND a.location_id = b.location_id
                           AND a.organization_id = c.organization_id
                           AND a.location_id = c.location_id) f,
                       rcv_lot_transactions g
                       (SELECT   trx_id, trx_line_id, trx_number,
                                 first_party_primary_reg_num gst_no,
                                 organization_id,
                                 SUM (rounded_tax_amt_tax_curr) tax_amount,
                                 trx_loc_line_id
                            FROM jai_tax_lines_all
                           WHERE trx_type = 'RECEIVE'
                             AND data_source = 'BASE_TABLE'
                             AND entity_code = 'RCV_TRANSACTION'
                             AND event_class_code = 'RECEIVING'
                             AND applied_from_entity_code =
                                                           'SALES_ORDER_ISSUE'
                             AND applied_from_event_class_code =
                                                           'SALES_ORDER_ISSUE'
                             AND TRUNC (trx_date) BETWEEN :fdate AND :tdate
                        GROUP BY trx_id,
                                 trx_line_id,
                                 trx_number,
                                 first_party_primary_reg_num,
                                 organization_id,
                                 trx_loc_line_id) i
                 ---wsh_new_deliveries  g
                WHERE  a.shipment_header_id = b.shipment_header_id
                   AND b.shipment_header_id = c.shipment_header_id
                   AND b.shipment_line_id = c.shipment_line_id
                   AND i.trx_id (+)= c.shipment_header_id
                   AND i.trx_line_id (+)= c.shipment_line_id
--                AND i.trx_line_id = c.shipment_line_id
--                AND i.trx_number = a.receipt_num
                   AND d.inventory_item_id = b.item_id
--                AND i.trx_number(+) = a.receipt_num
                   AND e.organization_id = a.ship_to_org_id
                   AND a.organization_id = f.organization_id
                   AND b.from_organization_id = f.organization_id
                   AND b.to_organization_id = e.organization_id
--                AND b.shipment_line_id=g.shipment_line_id
--                AND c.shipment_line_id=g.shipment_line_id
                   AND c.transaction_id = g.transaction_id(+)
                   AND c.transaction_id = i.trx_loc_line_id(+)
                   AND d.segment1 = h.appscode
                   AND a.ship_to_org_id = :orgid
                   AND c.transaction_type = 'RECEIVE'
                   AND TRUNC (c.transaction_date) BETWEEN :fdate AND :tdate
                   AND TRUNC (c.transaction_date) >= '04-nov-2011'
                   AND a.receipt_source_code = 'INTERNAL ORDER'
--                and a.receipt_num in ('216040')--206397
               )order by option1

                )

Query to Fetch Invoice details, HSN Code, Third party Registration number and GST Details.

The below query fetches the details of an Invoice along with the HSN Code of  a respective item present in an Invoice. GST details can also be fetched.


SELECT a.org_id,a.customer_trx_id,a.trx_number invno,a.trx_date, c.trx_line_id,
                         a.cust_trx_type_id,a.bill_to_customer_id,a.bill_to_site_use_id, b.line_number AS linenum,
                         b.inventory_item_id,d.segment1 itm,b.interface_line_attribute6 ,
                         SUBSTR(b.description,1,40) description1,SUBSTR(b.description,40,55)  description2,
                         (SELECT SUBSTR (REPLACE(f.template_name,' ',''),4,11) FROM jai_item_templ_hdr e,
                                                       jai_item_templ_hdr f
                                                 WHERE e.inventory_item_id = b.inventory_item_id
                                                   AND e.entity_id = f.template_hdr_id AND ROWNUM =1) hsn_code,
                          (SELECT SUBSTR (REPLACE(f.template_name,' ', ''),12,18) FROM jai_item_templ_hdr e,
                                                       jai_item_templ_hdr f
                                                 WHERE e.inventory_item_id = b.inventory_item_id
                                                   AND e.entity_id = f.template_hdr_id AND ROWNUM =1) hsn_code1,                       
                         b.uom_code,a.primary_salesrep_id,b.sales_order_line,a.interface_header_attribute1 ordernum,
                         TO_NUMBER(a.interface_header_attribute10) organization_id,B.quantity_invoiced,
                         b.unit_standard_price sprice,b.unit_selling_price,b.revenue_amount,b.extended_amount,
                         SUM(c.actual_tax_rate)tax_rate,SUM(c.unround_tax_amt_fun_curr)tax_amount,b.line_type,b.line_number,
--              DECODE(TAX_AMOUNT,0,0,null,0,0.00,0,VAT_ASSESSABLE_VALUE) taxgross,
                         NULL taxgross,warehouse_id, SUBSTR(NVL(d.attribute5,'   '),1,3) AS sch_category
               FROM      ra_customer_trx_all a,
                         ra_customer_trx_lines_all b,
                         jai_tax_lines_all c,
                         (SELECT * FROM mtl_system_items WHERE organization_id = 83) d
                 WHERE    a.customer_trx_id          = b.customer_trx_id
                   AND    a.trx_number                     = :p_trx_number
--                         AND b.interface_line_attribute6= c.line_id
                   AND    d.inventory_item_id               = b.inventory_item_id
                   AND    a.customer_trx_id                   = c.trx_id           
                   AND    b.customer_trx_line_id           = c.trx_line_id       
                   AND    b.line_type                              = 'LINE'
--                  AND a.trx_number = '11111700004'
                  AND a.interface_header_attribute1 IS NOT NULL
       GROUP BY a.org_id,a.customer_trx_id,a.trx_number ,a.trx_date, c.trx_line_id,
                         a.cust_trx_type_id,a.bill_to_customer_id,a.bill_to_site_use_id, b.line_number,
                         b.inventory_item_id,d.segment1,b.description,b.uom_code,B.quantity_invoiced,
                         b.unit_standard_price,b.unit_selling_price,b.revenue_amount,b.extended_amount,
                         c.actual_tax_rate,c.unround_tax_amt_fun_curr,b.interface_line_attribute6,
                         a.primary_salesrep_id,b.sales_order_line,a.interface_header_attribute1,a.interface_header_attribute10,
                         b.line_type,b.line_number,warehouse_id,d.attribute5



Query to fetch Third Party Registration Number(GST Number) :-


SELECT DISTINCT TRIM(TO_CHAR(B.registration_number))GST_NUM
       INTO  mcustvatno
       FROM  jai_party_regs a,
             jai_party_reg_lines b,
             ra_customer_trx_all rct,
             hz_cust_accounts bill_cus,
             hz_parties bill_party,
             hz_cust_site_uses_all hcs_bill,
             hz_cust_acct_sites_all hca_bill,
             hz_party_sites hps_bill,
             hz_locations hl_bill
    WHERE    1 = 1
      AND    a.party_reg_id                 = b.party_reg_id
      AND    a.party_type_code            = 'THIRD_PARTY_SITE'
      AND    b.regime_id                       = 10000
      AND    b.registration_type_code   = 'GST'
      AND    a.party_id                          = rct.bill_to_customer_id
      AND    rct.bill_to_customer_id    = bill_cus.cust_account_id
      AND    bill_party.party_id            = bill_cus.party_id
      AND    rct.bill_to_site_use_id      = hcs_bill.site_use_id
      AND    hcs_bill.site_use_code      = 'BILL_TO'
      AND    hca_bill.cust_acct_site_id = hcs_bill.cust_acct_site_id
      AND    hps_bill.party_site_id     =  hca_bill.party_site_id
      AND    a.party_site_id                =  hca_bill.cust_acct_site_id
      AND    hl_bill.location_id          =  hps_bill.location_id
      AND    rct.customer_trx_id        = :p_customer_trx_id;



Note:    Similarly we can get First Party Registration Number. In the above query replace the party_type_code as 'I/O' which will fetch you first party registration number.

Wednesday 25 October 2017

Return To Vendor Summay-GST

The below query fetches the data of purchase order wherein those items present in purchase order needs to be returned to Supplier because of some damage caused to goods etc..


SELECT rch.shipment_header_id,TRUNC(RCH.Creation_Date)Rec_Date,  rcl.shipment_line_id,
       RCH.receipt_num,RCT.organization_id,
       RCH.Attribute4 DC_No,RCH.Attribute6 DC_Date,NVL(RCL.QUANTITY_SHIPPED,0)Recd_Qty ,
       (RCT.Quantity)Returned_qty,(RCT.Transaction_date)Return_date ,
       RCT.unit_of_measure,RCT.vendor_id,RCT.Reason_id,RCT.Vendor_id,
--Lpad(RCT.ATTRIBUTE7,4,0)Del_Slip_no,
       RCT.ATTRIBUTE7 Del_Slip_no,
       RCL.item_id,RCL.item_Description||' '||hsnn.template_name item_Description, B.Segment1,VEN.Vendor_Name,LPAD(VEN.Segment1,5,0) Vendor_Code ,
       (RCT.Attribute5)No_Of_Pack,(RCT.ATTRIBUTE6)Qty_Per_Pack ,MTR.Description  Reason,
       SIT.vendor_site_id,SIT.vendor_site_code,SIT.Address_line1,SIT.Address_line2,SIT.Address_line3,SIT.city,SIT.state,SIT.zip,(
       SELECT jprl.registration_number
  FROM jai_party_regs jpr, ja.jai_party_reg_lines jprl
 where 1 = 1
   and party_id = SIT.vendor_id
   and party_site_id = SIT.vendor_site_id
   and jpr.party_reg_id = jprl.party_reg_id
   and jprl.regime_id=(SELECT jrc.regime_id
                       FROM JA.jai_regimes jrc
                       WHERE jrc.regime_code = 'GST'
                      )
                      )"GST Registration Number",
   RCT.PO_UNIT_PRICE, sum(jtla.ACTUAL_TAX_RATE) "GST%", (RCT.Quantity)*RCT.PO_UNIT_PRICE "RTV Value", sum(UNROUND_TAX_AMT_TAX_CURR) "Tax Amount"             
FROM rcv_shipment_headers    RCH,
     rcv_shipment_lines      RCL,
     rcv_transactions        RCT,
     mtl_system_items        B,
     HR_ORGANIZATION_UNITS_V HR ,
     po_vendors              VEN,
     po_vendor_sites_all SIT ,
     mtl_transaction_reasons MTR,
     jai_item_templ_hdr jith,
     jai_item_templ_hdr hsnn,
     jai_tax_lines_all jtla
WHERE RCH.shipment_header_id = RCL.shipment_header_id
AND   RCT.shipment_header_id = RCL.shipment_header_id
AND   RCH.shipment_header_id = RCT.shipment_header_id
AND   RCL.SHIPMENT_LINE_ID = RCT.SHIPMENT_LINE_ID
AND  TRUNC(RCT.CREATION_DATE)  BETWEEN :P_FROM_DT  AND   :P_TO_DATE
AND  RCT.ORGANIZATION_ID = :P_ORG_ID
--AND RCT.ATTRIBUTE7 BETWEEN NVL(:P_DC_NUM ,RCT.ATTRIBUTE7) AND NVL(:P_DC_NUM1 ,RCT.ATTRIBUTE7)
AND  RCL.item_id  =   B.Inventory_Item_id
AND  RCT.transaction_type = 'RETURN TO VENDOR'
AND  RCL.TO_ORGANIZATION_ID =B.ORGANIZATION_ID
AND  RCT.VENDOR_ID = VEN.vendor_id
AND  RCT.VENDOR_ID = SIT.vendor_id
AND rct.vendor_site_id=sit.vendor_site_id   
AND  RCH.receipt_source_code  ='VENDOR'
AND  RCT.organization_id = HR.Organization_id
AND  RCH.SHIP_TO_ORG_ID= HR.Organization_id
AND  RCT.reason_id = MTR.Reason_id (+)
AND  sit.inactive_date IS NULL
and  jith.inventory_item_id(+) =  B.Inventory_Item_id
and    jith.entity_type_code = 'ITEM_TEMPL_ASGN'
and    jith.entity_id = hsnn.template_hdr_id
and    hsnn.entity_type_code = 'ITEM_TEMPL'
--and    jith.organization_id = RCH.SHIP_TO_ORG_ID
and    jtla.entity_code = 'RCV_TRANSACTION'
--and    rch.shipment_header_id = rct.shipment_header_id
--AND    jtla.organization_id = rch.ship_to_org_id
--and    jtla.trx_number = rch.receipt_num
AND    jtla.trx_loc_line_id(+) = rct.transaction_id
AND jtla.trx_id(+) = rct.shipment_header_id
AND jtla.trx_line_id(+) = rct.shipment_line_id
GROUP BY SIT.vendor_id, hsnn.template_name, RCT.PO_UNIT_PRICE,
         rch.shipment_header_id,TRUNC(RCH.Creation_Date),
         RCH.receipt_num, RCT.organization_id,
         RCH.Attribute4 ,RCH.Attribute6,(RCT.Transaction_date),
         (RCT.Attribute5) ,(RCT.ATTRIBUTE6) ,RCT.ATTRIBUTE7,MTR.Description ,
          RCT.unit_of_measure,RCT.vendor_id,RCT.Reason_id,RCT.Vendor_id,
         RCL.item_id,RCL.item_Description,B.Segment1,VEN.Vendor_Name,LPAD(VEN.Segment1,5,0) ,
         SIT.vendor_site_id,SIT.vendor_site_code,SIT.Address_line1,SIT.Address_line2,SIT.Address_line3,SIT.city,SIT.state,SIT.zip, RCT.Quantity, RCL.QUANTITY_SHIPPED, rcl.shipment_line_id
ORDER BY RCH.receipt_num

TAX INVOICE RCM(REVERSE CHARGE MECHANISM) - GST

The below query fetches the details of a tax invocie  where in Reverse charge mechanism(RCM) is applied in order to claim the extra tax paid by an organization along with the GST details .



SELECT AIL.description,AIL.amount,1 quantity,AIA.invoice_num,AIA.invoice_date,APS.vendor_name,ASSA.address_line1,ASSA.address_line2,ASSA.address_line3,ASSA.city,ASSA.state, ASSA.zip, JTL.first_party_primary_reg_num,J TL.third_party_primary_reg_num,
 OOD.organization_id,  OOD.organization_name, AIA.invoice_amount, HL.address_line_1 address_1, HL.address_line_2 address_2, HL.address_line_3 address_3,HL.town_or_city, HL.postal_code, HL.region_1,(SELECT TAR.rev_seqno  FROM 
                                                      ,org_organization_definitions OOD
                                                                              ,ap_invoices_all AIA
                                                                               WHERE 1=1
                                                                               AND AIA.invoice_id           = TAR.ap_invoice_id
                                                                              AND OOD.organization_id   = TAR.organization_id
                                                                              AND OOD.ORGANIZATION_ID  = :p_org_id
                                                                              AND AIA.INVOICE_NUM            = :p_invnum ) rev_seqno
FROM ap_invoices_all AIA
    ,ap_invoice_lines_all AIL
    ,ap_suppliers APS
    ,ap_supplier_sites_all ASSA
    ,jai_tax_lines_all JTL
    ,org_organization_definitions OOD
    ,hr_locations HL
WHERE 1=1
AND AIA.invoice_id         = AIL.invoice_id
AND AIA.vendor_id          = ASSA.vendor_id
AND APS.vendor_id          = ASSA.vendor_id
AND AIA.vendor_site_id   = ASSA.vendor_site_id
AND AIA.vendor_id           = JTL.party_id
AND AIA.invoice_id          =  JTL.trx_id
AND AIL.line_number        = JTL.trx_line_number
AND JTL.organization_id   =  OOD.organization_id
AND OOD.organization_id  = HL.inventory_organization_id
-- AND AIA.invoice_id        =3904571
AND OOD.ORGANIZATION_ID  = :p_org_id
AND AIA.INVOICE_NUM      = :p_invnum
GROUP BY AIL.description, AIL.amount, AIA.invoice_num, AIA.invoice_date, APS.vendor_name, ASSA.address_line1,ASSA.address_line2,ASSA.address_line3,ASSA.city,ASSA.state,
ASSA.zip,JTL.first_party_primary_reg_num,JTL.third_party_primary_reg_num,
OOD.organization_id, OOD.organization_name, AIA.invoice_amount, HL.address_line_1, HL.address_line_2,HL.address_line_3,HL.town_or_city,  HL.postal_code, HL.region_1

Tuesday 24 October 2017

Delivery Challan with GST Details

Query retrieves data of those inventory items which are used for promotional purposes or given as a sample items for a sales executive in order to promote the products along with the GST Details(GSTIN Number, etc...)



SELECT dcno, dcdate, description, to_organization, transport, lrno, lrdate, shippers, subinventory_code, qty,attribute10,
transaction_uom, transaction_cost,actual_cost, appscode, item_description, lot_number, mfgdt, expdt, to_org, to_add1, to_add2,
to_add3, to_city, to_pin, to_state, to_dl1, to_dl2, fm_org, fm_add1, fm_add2, fm_add3, fm_city, fm_pin, fm_state,
fm_dl1, fm_dl2, fm_lst_no, fm_cst_no, to_lst_no, to_cst_no,(actual_cost* qty) value,
(select registration_number from jai_party_regs a, jai_party_reg_lines b, hr_locations c
where 1=1
and a.party_reg_id = b.party_reg_id
and a.party_type_code = 'IO'
and b.regime_id = 10000
and b.registration_type_code = 'GST'
and a.party_id  = c.inventory_organization_id
and a.party_site_id = c.location_id
and c.inventory_organization_id = :orgid)gst_in,hsn_code
from
(select a.header_id as dcno,  trunc(b.transaction_date) as dcdate, a.description, 
a.attribute1 as to_organization, a.attribute15 as transport, substr(a.attribute2,1,15) as lrno,
substr(a.attribute3,1,15) as lrdate, nvl(to_number(attribute4),0) as shippers,
b.subinventory_code, (d.transaction_quantity * -1) as qty, b.transaction_uom,b.transaction_cost, b.actual_cost,a.attribute10,
c.segment1 as appscode, c.description as item_description,
e.lot_number, (e.expiration_date - c.shelf_life_days) as mfgdt, e.expiration_date as expdt,
f.location_code as to_org, f.address_line_1 as to_add1, f.address_line_2 as to_add2, f.address_line_3 as to_add3,
f.town_or_city as to_city, f.postal_code as to_pin, f.region_1 as to_state,
f.loc_information14 as to_dl1, f.loc_information16 as to_dl2,
g.location_code as fm_org, g.address_line_1 as fm_add1, g.address_line_2 as fm_add2, g.address_line_3 as fm_add3,
g.town_or_city as fm_city, g.postal_code as fm_pin, g.region_1 as fm_state,
g.loc_information14 as fm_dl1, g.loc_information16 as fm_dl2,
h.st_reg_no as fm_lst_no, h.cst_reg_no as fm_cst_no,
to_lst_no, to_cst_no,
(SELECT SUBSTR (REPLACE(f.template_name,' ',''),4,15) FROM jai_item_templ_hdr e,
                                                       jai_item_templ_hdr f
                                                 WHERE 1=1
                                                   AND e.inventory_item_id = b.inventory_item_id
                                                   AND e.entity_id = f.template_hdr_id
                                                   AND e.entity_type_code = 'ITEM_TEMPL_ASGN'
                                                   AND e.organization_id = :orgid
                                                   AND ROWNUM =1)hsn_code
from mtl_txn_request_headers a,
(select transaction_id, transaction_source_id, inventory_item_id, subinventory_code,  transaction_date,
transaction_quantity, transaction_uom, transaction_cost,actual_cost,attribute10
from mtl_material_transactions
where organization_id = :orgid
and transaction_type_id = 63) b,
(select segment1, description, inventory_item_id, shelf_life_days from mtl_system_items
where organization_id = :orgid) c,
(select transaction_id, transaction_source_id, inventory_item_id, lot_number, transaction_quantity
from mtl_transaction_lot_numbers where organization_id = :orgid) d,
(select inventory_item_id, lot_number, expiration_date
from mtl_lot_numbers
where organization_id = :orgid) e,
(select location_id, location_code, address_line_1, address_line_2, address_line_3, town_or_city, postal_code, region_1,
loc_information14, loc_information16
from hr_locations) f,
(select location_code, address_line_1, address_line_2, address_line_3, town_or_city, postal_code, region_1,
loc_information14, loc_information16, inventory_organization_id
from hr_locations) g,
(select organization_id, st_reg_no, cst_reg_no
from jai_cmn_inventory_orgs  
where location_id = 0) h,
(select location_id, organization_id, st_reg_no as to_lst_no, cst_reg_no as to_cst_no
from jai_cmn_inventory_orgs ) i 
where a.organization_id = :orgid
and a.header_id between :fdcno and :tdcno
and a.transaction_type_id = 63
and a.header_id = b.transaction_source_id
and b.inventory_item_id = c.inventory_item_id
and d.transaction_id = b.transaction_id
and d.transaction_source_id = a.header_id
and d.inventory_item_id = e.inventory_item_id
and d.lot_number = e.lot_number
and ltrim(a.attribute1) = ltrim(f.location_code)
and a.organization_id = g.inventory_organization_id
and h.organization_id = a.organization_id
and f.location_id = i.location_id
union
select a.header_id as dcno,  b.transaction_date as dcdate, a.description, 
a.attribute1 as to_organization, a.attribute15 as transport, a.attribute2 as lrno, a.attribute3 as lrdate, to_number(a.attribute4) as shippers,
b.subinventory_code, (d.transaction_quantity * -1) as qty, b.transaction_uom, b.transaction_cost,b.actual_cost,a.attribute10,
c.segment1 as appscode, c.description as item_description,
e.lot_number, (e.expiration_date - c.shelf_life_days) as mfgdt, e.expiration_date as expdt,
f.location_code as to_org, f.address_line_1 as to_add1, f.address_line_2 as to_add2, f.address_line_3 as to_add3,
f.town_or_city as to_city, f.postal_code as to_pin, f.region_1 as to_state,
f.loc_information14 as to_dl1, f.loc_information16 as to_dl2,
g.location_code as fm_org, g.address_line_1 as fm_add1, g.address_line_2 as fm_add2, g.address_line_3 as fm_add3,
g.town_or_city as fm_city, g.postal_code as fm_pin, g.region_1 as fm_state,
g.loc_information14 as fm_dl1, g.loc_information16 as fm_dl2,
h.st_reg_no as fm_lst_no, h.cst_reg_no as fm_cst_no,
' ' to_lst_no, ' ' to_cst_no,
(SELECT SUBSTR (REPLACE(f.template_name,' ',''),4,15) FROM jai_item_templ_hdr e,
                                                       jai_item_templ_hdr f
                                                 WHERE 1=1
                                                   AND e.inventory_item_id = b.inventory_item_id
                                                   AND e.entity_id = f.template_hdr_id
                                                   AND e.entity_type_code = 'ITEM_TEMPL_ASGN'
                                                   AND e.organization_id = :orgid
                                                   AND ROWNUM =1)hsn_code
from mtl_txn_request_headers a,
(select transaction_id, transaction_source_id, inventory_item_id, subinventory_code,  transaction_date,
transaction_quantity, transaction_uom, transaction_cost,actual_cost,attribute10
from mtl_material_transactions
where organization_id = :orgid
and transaction_type_id = 63) b,
(select segment1, description, inventory_item_id, shelf_life_days from mtl_system_items
where organization_id = :orgid) c,
(select transaction_id, transaction_source_id, inventory_item_id, lot_number, transaction_quantity
from mtl_transaction_lot_numbers where organization_id = :orgid) d,
(select inventory_item_id, lot_number, expiration_date
from mtl_lot_numbers
where organization_id = :orgid) e,
(select location_id, location_code, address_line_1, address_line_2, address_line_3, town_or_city, postal_code, region_1,
loc_information14, loc_information16, inventory_organization_id, object_version_number
from hr_locations) f,
(select location_code, address_line_1, address_line_2, address_line_3, town_or_city, postal_code, region_1,
loc_information14, loc_information16, inventory_organization_id
from hr_locations) g,
(select organization_id, st_reg_no, cst_reg_no
from jai_cmn_inventory_orgs  
where location_id = 0) h
where a.organization_id = :orgid
and a.header_id between :fdcno and :tdcno
and a.transaction_type_id = 63
and a.header_id = b.transaction_source_id
and b.inventory_item_id = c.inventory_item_id
and d.transaction_id = b.transaction_id
and d.transaction_source_id = a.header_id
and d.inventory_item_id = e.inventory_item_id
and d.lot_number = e.lot_number
and ltrim(a.attribute1) = ltrim(f.location_code)
and a.organization_id = g.inventory_organization_id
and h.organization_id = a.organization_id
and f.inventory_organization_id is null)
order by dcno

Thursday 19 October 2017

Oracle Fusion HCM Data Loader (HDL) Keys Significance

HCM Data Loader (HDL) Keys

Supported Key Types:
HCM Data Loader (HDL) supports 4 different types of keys as listed below 
  1. GUID – Oracle Fusion Global Unique ID
  2. Oracle Fusion Surrogate ID
  3. Source Keys
  4. User Keys 
Key Resolution Sequence (Key references are resolved) in the following order:

1. Oracle Fusion GUID

2. Source key

3. Oracle Fusion surrogate ID

4. User key

Important Notes 
1. While  Create/First time Loading any object(s) we have to consider only SOURCE KEY / USER KEY no need to think about GUID or Surrogate ID (because those are created only after an object created in fusion).
2.While Modify/Updating/DELETE any object then all four keys comes into picture.

If you supply multiple key values, then they are used in this order with no cross-validation. For example, if you supply both a GUID and a source key, then the GUID is used to identify the record and the source key is ignored. If the source key references a different record from the GUID, then no error is raised.
These key types are explained below:

Oracle Fusion GUID
Integration Key Generated by Fusion
  • Generated in Oracle Fusion when a record is created
  • Hexadecimal value
  • Unique across all objects
  • Held in Integration Key Map
Oracle Fusion Surrogate ID
Fusion Generated Unique ID
  • Generated in Oracle Fusion when the record is created
  • Numeric value
  • Unique only for the object type
  • Held on the object
Source Keys
Source System Key Information
  • Two values combined:
    • SourceSystemOwner
    • SourceSystemID
  • Held in Integration Key Map

User Keys
User Readable and Generated Keys
  • Natural values
  • One or many attributes
  • Sometimes alternatives
  • Sometimes updateable
  • Held on the object definition
     

Key Type
Create
Update
Held on Object
Type
Generated Automatically
GUID
No
Yes
No
Hexadecimal
Yes
Surrogate ID
No
Yes (see note #1)
Yes
Numeric
Yes
Source Key
Yes
Yes
No
Alphanumeric
Conditionally (see note #2)
User Key
Yes
Yes (see note #3)
Yes
Alphanumeric
No
Notes:-
  1. You can use surrogate IDs when updating objects, but the IDs may not be readily available to Oracle HCM Cloud users.
  2. Default source keys are generated only if you don't supply a source key when creating an object.
  3. You can't use user keys alone when updating some objects because their values are subject to change.
  4. Keys that aren't held on the object exist in the Integration Key Map table.
Integration Key Map table
Keys that aren’t held on the object are stored in the HDL integration key map table - HRC_INTEGRATION_KEY_MAP. You should be able to use BIP to fire up a SQL statement and view the contents of this table. e.g. 
/* Formatted on 2017/09/12 14:25 (Formatter Plus v4.8.8) */
SELECT object_name
     ,source_system_id
     ,source_system_owner
     ,surrogate_id
     ,RAWTOHEX (guid) guid
FROM fusion.hrc_integration_key_map
WHERE source_system_owner = 'STUDENT1'


Business Object
Fusion GUID
Source Key
Surrage ID
User Key
Location
88ABCD164738983
STUDENT1_LOC1
 8899933355517
 (Set code and Location Code) COMMON, HQ1
  • Fusion GUID: System generated GUID
  • Source Key:  Source System Owner is the reference to source application like PS or EBS, Source System Key is the actual key\id provided in the Location.dat file. 
  • Surrogate ID: System generated. In this case it is the primary key from locations record. e.g. select * from PER_LOCATION_DETAILS_F_VL  where location code = 'STUDENT1 Location1‘   (Result Location ID = 8899933355517)
  • User Key:- Best way to get this info is the Business Object Documentation from MOS or other option is UI as shown below. Online page should highlight user keys with