Query to retrieve Invoice Holds

SELECT vnd.vendor_name, aia.invoice_num, pha.segment1 po_number, pla.line_num, ah.hold_lookup_code, ah.release_lookup_code,

   (nvl((pda.quantity_ordered - nvl(pda.quantity_cancelled, 0)) * pll.price_override, 0)) AS ordered_amount,

   (pda.amount_billed) AS billed_amount,

   (pda.amount_billed - (nvl((pda.quantity_ordered - nvl(pda.quantity_cancelled, 0)) * pll.price_override, 0))) difference,

   pda.quantity_ordered, pda.quantity_delivered

FROM

    apps.ap_holds_all ah,

    apps.po_line_locations_all pll,

    apps.ap_invoices_all aia,

    apps.po_distributions_all pda,

    apps.po_lines_all pla,

    apps.po_headers_all pha,

    apps.ap_suppliers vnd

WHERE ah.line_location_id = pll.line_location_id

    AND ah.invoice_id = aia.invoice_id

    AND pla.po_line_id = pll.po_line_id

    AND pda.line_location_id = pll.line_location_id

    AND pha.po_header_id = pla.po_header_id

    AND ah.creation_date BETWEEN (SYSDATE - 120) AND SYSDATE

    AND vnd.vendor_id = pha.vendor_id

    ORDER BY vendor_name, invoice_num, pha.segment1, pla.line_num;

Query to find iSupplier Users list

Query to find iSupplier Users list


SELECT WEB_USER_ID, USR.USER_NAME, USR.EMAIL_ADDRESS, AP_SUPP.VENDOR_NAME
  FROM AK_WEB_USER_SEC_ATTR_VALUES A, AP_SUPPLIERS AP_SUPP, FND_USER USR
 WHERE ATTRIBUTE_CODE = 'ICX_SUPPLIER_ORG_ID'
   AND ATTRIBUTE_APPLICATION_ID = 177
   AND NUMBER_VALUE = AP_SUPP.VENDOR_ID
   AND USR.USER_ID = A.WEB_USER_ID;

BI Publisher: Apply formatting on Group of rows based on data

Output Needed:

Quick Summary of Steps:

Detail of each step:

1. InitVarBIDNO
<?xdoxslt:set_variable($_XDOCTX, 'PREV_BIDNO', '0')?>


2. InitVarCount: 
<?xdoxslt:set_variable($_XDOCTX, 'XXCOUNT', 1)?>


3. For Each
<?for-each:G_BOARD_DOC?>  
<?sort:MASTER_RFQ_NUM;'ascending';data-type='text'?> 
<?sort:BDOC_TYPE;'descending';data-type='text'?> 
<?sort:VENDOR_NAME;'ascending';data-type='text'?>

4.UpdateBdocCount
<?if:xdoxslt:get_variable($_XDOCTX,'PREV_BIDNO')!=MASTER_RFQ_NUM?><?xdoxslt:set_variable($_XDOCTX, 'XXCOUNT', xdoxslt:get_variable($_XDOCTX,'XXCOUNT')+1)?><?end if?>


5. IfSameBdocAsPreviousRow
<?if@row:xdoxslt:get_variable($_XDOCTX,'XXCOUNT') mod 2=0?>
<xsl:attribute name="color" xdofo:ctx="incontext">rgb(153, 0, 0)</xsl:attribute>
<?end if?>


6. UpdatePrevValue
<?xdoxslt:set_variable($_XDOCTX, 'PREV_BIDNO',MASTER_RFQ_NUM)?>